MongoDB - unpack rows from a embedded document into their own column

Question

The Problem

So I have a MongoDB document that looks like so:

Awards:

    [
      {
        "year": "2017",
        "winners": [
          {
            "name": "james",
            "id": 1
          },
          {
            "name": "adam",
            "id": 2
          }
        ]
      },
      {
        "year": "2018",
        "winners": [
          {
            "name": "mary",
            "id": 3
          },
          {
            "name": "jane",
            "id": 4
          }
        ]
      }
    ]

And I'm basically trying to expand out the winners from each year so that the schema is roughly (year, winner_id) i can later use that for a join I plan on doing. Something like this:

[
   {
     "year":"2017",
     "winner_id":1
   },   
   {
     "year":"2017",
     "winner_id":2
   }, 
   {
     "year":"2018",
     "winner_id":3
   },
   {
     "year":"2018",
     "winner_id":4
   }
]

What I've Tried

so intuitively, I'm looking for something like $unwind, but the problem is that winners is not an array.

• I tried db.awards.aggregate({'$unwind':'$winners.id'}) but that didn't work. (gave an empty result)

• So afterwards I thought db.awards.aggregate({'$unwind': {'$objectToArray':'$winners.id'}}) but that didn't work either. (gave an error)

• According to this post I should do this:

db.awards.aggregate(
 { '$project': {
    _id: 1,
    'winners': 1
  } },
{'$unwind': '$winners'},
{'$unwind': '$winners.id'}
)

but even that didn't work, I still get the embedded document for each winner

Any thoughts?

Answer 1

Your data is invalid JSON. In assume that winners attribute is very likely an array.

I can see three additional issues in your aggregation commands:

1. You should pass the aggregation pipeline as an array as well to the aggregate function.
2. the syntax for $project is different in aggregation pipelines. You do not define which fields should be included. You do define the actual mapping
3. I do not understand why you wnat to unwind the winners id. The attribute to unwind should be an array and in your example there is only the winners array.

I think it is easier to do the $unwind before projection

db.awards.aggregate(
  [
    {
      $unwind: { path: '$winners' }
    },
    {
      $project: {
        year: '$year',
        winner_id: '$winners.id'
      }
    }
  ]
);

Mongo Playground