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?

Your posted documents are not valid json. Please repost them correctly — Ashh, Nov 15 '19 at 07:18
the `winners` attribute is still an object and not an array. Therefore it is still invalid JSON. Are you really sure that `winners` is not an array? — thomas, Nov 15 '19 at 07:30
yes. it's an array of embedded objects. That's kinda my problem :/ — JoeVictor, Nov 15 '19 at 07:36

score 1 · Answer 1 · answered Nov 15 '19 at 07:32

1

You need to first $unwind the winners and use $replaceRoot to get it at ROOT position

db.collection.aggregate([
  { "$unwind": "$winners" },
  { "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          "$winners",
          { "year": "$year" }
        ]
      }
    }
  }
])

MongoPlayground

answered Nov 15 '19 at 07:32

Ashh

44,693
14
105
132

1

I like this one, replaceRoot is very powerful and this finally explained to me what it does! – JoeVictor Nov 15 '19 at 07:57

thomas · Accepted Answer · 2019-11-15T07:45:10.610

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:

You should pass the aggregation pipeline as an array as well to the aggregate function.
the syntax for $project is different in aggregation pipelines. You do not define which fields should be included. You do define the actual mapping
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

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

The Problem

What I've Tried

2 Answers2