unwind and group for the nested array objects in mongodb

Question

In mongodb, I have the data structure like this:

{
    "data" : [ 
        {
            "car" : [ 
                {
                    "model" : "aaa",
                }, 
                {
                    "model" : "bbb",
                }, 
                {
                    "model" : "ccc",
                }
            ],
            "user" : {
                "id" : "123"
            }
        }
    ],
}

And I want to group and count the car model field. So first I tried the unwind method like this:

.aggregate([
  { $project: {"data.car.model":1, "data.user.id":1} },
  { $unwind: {
      path: "$data.car",
      preserveNullAndEmptyArrays: true
      } },
  { $group: {
      _id: "$data.car.model",
      count: { $sum: 1 }
      } }
])

But the result is:

{
    "_id" : [ ["aaa", "bbb", "ccc"] ],
    "count" : 1.0
}

which is not what I wanted since the model didn't unwind. The result I'm hoping is:

[{_id:"aaa",count:1}, {_id:"bbb",count:1}, {_id:"ccc", count:1}]

Then by looked at mongodb unwind array nested inside an array of documents, I tried the $project the nested array:

.aggregate([
  { $project: {"car":"$data.car.model", "user":"$data.user.id"} },
])

But the result is:

{
    "car" : [ ["aaa", "bbb", "ccc"] ],
    "user" : ["123"]
}

As you can see, the car and user was embedded in an array, which I still can't apply the $unwind method. However, I hope it should be like:

{
    "car" : ["aaa", "bbb", "ccc"],
    "user" : "123"
}

The mongo I'm using is 3.2, I searched a lot on the web but didn't find the answer. Is there possible to do so?

Neil Lunn · Accepted Answer · 2018-05-25 12:00:32Z

11

You need to $unwind "each" array independently:

.aggregate([
  { "$unwind": { "path": "$data", "preserveNullAndEmptyArrays": true } },
  { "$unwind": { "path": "$data.car", "preserveNullAndEmptyArrays": true } },
  { "$group": {
    "_id": "$data.car.model",
    "count": { "$sum": 1 }
  }}
])

You cannot actually "reach into" and $unwind as you attempted. So you first "un-wrap" the outer array, and then the "inner" one. Then the documents are flattened for grouping.

An alternate approach may be to use $reduce to "flatten the array" before "unwinding":

.aggregate([
  { "$project": {
    "data": {
      "$reduce": {
        "input": "$data.car",
        "initialValue": [],
        "in": {
          "$concatArrays": [ "$$value", "$$this" ]
        }
      }
    }
  }},
  { "$unwind": "$data" },
  { "$group": {
    "_id": "$data.model",
    "count": { "$sum": 1 }
  }}
])

But it would not really be recommended since the "projection" more likely involves a higher cost than that incurred by using $unwind for each level of nesting in the array.

edited May 25, 2018 at 12:00

answered May 25, 2018 at 9:28

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

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

1 Comment

Harshal Yeole Over a year ago

Better and clean solution. +1 (y)

Collectives™ on Stack Overflow

unwind and group for the nested array objects in mongodb

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related