Aggregate nested objects

I have two collections in my mongoDb database, one called media, one called users.

Documents in the media collection:

{"_id":{"$oid":"6379204a8cf5677554c26c1b"},"_partition":"6378eb74f6613d5d4192da79","name":"silas test"}
{"_id":{"$oid":"6378eb74f6613d5d4192da79"},"_partition":"6378eb74f6613d5d4192da79","name":"test media"}

Document in the users collection:

{
    "_id":{
    "$oid":"6379ee6c770a8f43afc8e3e4"},
    "_partition":"6378eb74f6613d5d4192da79",
    "types":[
        {
            "mediaReference":[
               {"$oid":"6379204a8cf5677554c26c1b"}
            ],
            "mediatype":"podcast"
        },
        {
            "mediaReference":[
                {"$oid":"6378eb74f6613d5d4192da79"}
            ],
            "mediatype":"movie"
        }
    ],
    "username":"silas"
}

Now im looking for a way to use the mongoDb aggregation pipeline to get the following result:

{
    "_id":{
    "$oid":"6379ee6c770a8f43afc8e3e4"},
    "_partition":"6378eb74f6613d5d4192da79",
    "types":[
        {
            "mediaReference":[
                {
                   "_id": {
                       "$oid":"6379204a8cf5677554c26c1b"
                   },
                   "_partition":"6378eb74f6613d5d4192da79",
                   "name":"silas test"
                }
            ],
            "mediatype":"podcast"
        },
        {
            "mediaReference":[
                {
                    "_id": {
                        "$oid":"6378eb74f6613d5d4192da79"
                    },
                    "_partition":"6378eb74f6613d5d4192da79",
                    "name":"test media"
                }
            ],
            "mediatype":"movie"
        }
    ],
    "username":"silas"
}

Basically im searching for a way to paste the refferenced document into the mediaReference object inside the users document.

i would appreciate any help

Hi @Silas_Jeydo ,

I have to say that it sounds like you should review your data mode, if you need the data to be queried together you should consider storing it together, even if it means duplicating data from “media” into users collection.

However the following query will do what you need but its over-complex in my opinion and might be resource intesive:

db.users.aggregate([{
 $unwind: {
  path: '$types'
 }
}, {
 $lookup: {
  from: 'media',
  localField: 'types.mediaReference',
  foreignField: '_id',
  as: 'types.mediaReference'
 }
}, {
 $group: {
  _id: '$_id',
  merged: {
   $first: '$$ROOT'
  },
  types: {
   $push: '$types'
  }
 }
}, {
 $addFields: {
  'merged.types': '$types'
 }
}, {
 $replaceRoot: {
  newRoot: '$merged'
 }
}])

Thanks
Pavel

Thank you for the fast response!

My reason for choosing this type of data model was that one user could store for example 100 podcasts and 100 movies in the media collection. To put all of them together in one document seemed too big for me.
Also one document in the media collection which has currently only the id, partiton and a name in it will have many more object entries.
Considering this, would you still restructure the data model or use the aggregation pipeline?

Thank you for your help

Hi @Silas_Jeydo ,

I don’t think that 200 elements in a document with this amount of sub fields is big or unadvisable.

We do want to contain arrays under 1000 elements.

We offer an outlier pattern to deal with large specific use cases that requires data breaking into multiple buckets:

Thanks
Pavel