MongoDB multiple levels embedded array query

Question

I have a document like this:

{
 _id: 1,
 data: [
        {
           _id: 2,
           rows: [
                  {
                   myFormat: [1,2,3,4]
                  },
                  {
                   myFormat: [1,1,1,1]
                  }
                 ]
        },
        {
           _id: 3,
           rows: [
                  {
                   myFormat: [1,2,7,8]
                  },
                  {
                   myFormat: [1,1,1,1]
                  }
                 ]
        }
       ]
},

I want to get distinct myFormat values as a complete array.

For example: I need the result as: [1,2,3,4], [1,1,1,1], [1,2,7,8]

How can I write mongoDB query for this?

Thanks for the help.

whoami - fakeFaceTrueSoul · Accepted Answer · 2019-08-25 22:49:23Z

2

Please try this, if every object in rows has only one field myFormat :

db.getCollection('yourCollection').distinct('data.rows')

Ref : mongoDB Distinct Values for a field

Or if you need it in an array & also objects in rows have multiple other fields, try this :

db.yourCollection.aggregate([{$project :{'data.rows.myFormat':1}},{ $unwind: '$data' }, { $unwind: '$data.rows' },
{ $group: { _id: '$data.rows.myFormat' } },
{ $group: { _id: '', distinctValues: { $push: '$_id' } } },
{ $project: { distinctValues: 1, _id: 0 } }])

Or else:

db.yourCollection.aggregate([{ $project: { values: '$data.rows.myFormat' } }, { $unwind: '$values' }, { $unwind: '$values' },
{ $group: { _id: '', distinctValues: { $addToSet: '$values' } } }, { $project: { distinctValues: 1, _id: 0 } }])

Above aggregation queries would get what you wanted, but those can be tedious on large datasets, try to run those and check if there is any slowness, if you're using for one-time then if needed you can consider using {allowDiskUse: true} & irrespective of one-time or not you need to check on whether to use preserveNullAndEmptyArrays:true or not.

Ref : allowDiskUse , $unwind preserveNullAndEmptyArrays

edited Aug 25, 2019 at 22:49

answered Aug 25, 2019 at 21:14

whoami - fakeFaceTrueSoul

18.1k6 gold badges35 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user9275416 Over a year ago

Thanks a lot, it actually worked. I will read more about aggregation.

user9275416 Over a year ago

Can you please tell how I can write query based on myFormat and then get only the documents inside rows which has that myFormat? I have edited the question also. Thanks for the help.

whoami - fakeFaceTrueSoul Over a year ago

@user9275416 : ok basic thing in SO, Once a question is solved, Please accept it to close it, do not edit it to make it complex. If there are minor changes then it should be ok, but please don't put in entire new requirement into the same question that's makes everything look complicated, Raise a new question if there is no other question that matches your req !! Please give me sometime on this new one..

user9275416 Over a year ago

Okay Understood

Collectives™ on Stack Overflow

MongoDB multiple levels embedded array query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related