flatten aggregation result

806 views

Skip to first unread message

arjen walstra

unread,

Nov 30, 2016, 6:01:04 AM11/30/16

to mongodb-user

I am struggling to find a solution for the following problem.

I have a collection 'test' with many documents. these documents contain an array of documents.

i want to retrieve a single array containing all "embedded field" of the arrays of all the matching documents.

the closest i can get so for is by using the aggregation method in robomongo:

db.getCollection('test').aggregate([{$match:{"bom.COMPONENT":"101-00001-017"}},{$project:{"CicodeList.Cicode":1}}])

which yield me the following result:

what i would like to get is something like this json result

{list:["123","456","789",....]}

Kevin Adistambha

unread,

Dec 13, 2016, 1:02:59 AM12/13/16

to mongodb-user

I have a collection ‘test’ with many documents. these documents contain an array of documents.
i want to retrieve a single array containing all “embedded field” of the arrays of all the matching documents.

the closest i can get so for is by using the aggregation method in robomongo:

db.getCollection(‘test’).aggregate([{$match:{“bom.COMPONENT”:”101-00001-017”}},{$project:{“CicodeList.Cicode”:1}}])

Assuming your collection looks like this:

> db.test.find()
{
  "_id": ObjectId("584f8b56272a55975ce74b8a"),
  "CicodeList": [
    {
      "Cicode": 123
    },
    {
      "Cicode": 456
    },
    {
      "Cicode": 789
    }
  ]
}
{
  "_id": ObjectId("584f8cdc272a55975ce74b8b"),
  "CicodeList": [
    {
      "Cicode": 234
    },
    {
      "Cicode": 345
    },
    {
      "Cicode": 456
    }
  ]
}

instead of using $project, you can use $group instead. The $group operator has a $push operation that allows you to construct an array. For example (using the mongo shell):

> db.test.aggregate([

    // unwind the CicodeList array (https://docs.mongodb.com/v3.2/reference/operator/aggregation/unwind/)
    {$unwind:'$CicodeList'},

    // group by using a null _id
    {$group:{_id:null, list:{$push:'$CicodeList.Cicode'}}}
])

Grouping by _id: null field will output a single document containing an array of Cicode values:

{
    "_id": null,
    "list": [
        123,
        456,
        789,
        234,
        345,
        456
    ]
}

You can change the _id field in the $group to change the grouping that you require. Please see the $group manual for more details: https://docs.mongodb.com/v3.2/reference/operator/aggregation/group/