MongoDB aggregation with nested arrays of objects

Question

I'm striving to get some aggregated data from Mongo DB. I have the following collections:

Restaurants:

{
        "_id" : ObjectId("5e0ff6d424f9fc12bc3d9464"),
        "name" : "Pizzaria Don Juan",
        "active" : true,
        "users" : [
                {
                        "_id" : ObjectId("5e10fc2adc147a373c312144")
                },
                {
                        "_id" : ObjectId("5e11ff8003eb832ef84342a6")
                }
        ],
        "socialMedias" : [
                {
                        "_id" : ObjectId("5e1008943330ad05d4e1867c"),
                        "url" : "https://instagram/jetpizzas"
                },
                {
                        "_id" : ObjectId("5e10089a3330ad05d4e1867d"),
                        "url" : "https://facebook.com/jetpizzas"
                }
        ],
        "branches" : [
                {
                        "name" : "Teste"
                },
                {
                        "name" : "Teste 2"
                }
        ],
        "sections" : [
                {
                        "name" : "Bebidas"
                }
        ],
        "__v" : 0
}
{
        "_id" : ObjectId("5e0ffd23991918424c8d7c3b"),
        "name" : "Pizza Ruth",
        "active" : true,
        "users" : [ ],
        "socialMedias" : [ ],
        "branches" : [ ],
        "sections" : [ ],
        "__v" : 0
}
{
        "_id" : ObjectId("5e0ffd3d991918424c8d7c3c"),
        "name" : "Feijão de Corda",
        "active" : true,
        "users" : [ ],
        "socialMedias" : [ ],
        "branches" : [ ],
        "sections" : [ ],
        "__v" : 0
}

Users

{
        "_id" : ObjectId("5e10fc2adc147a373c312144"),
        "isExpired" : false,
        "isBlocked" : false,
        "loginTentatives" : 0,
        "profile" : 2,
        "active" : true,
        "username" : "[email protected]",
        "password" : "$2a$10$xhmw83QXbMvSqmrKAUYn.O4fOxboEyVkVB0DGkSsJUOp7K4bYQkCm",
        "email" : "",
        "phone" : "",
        "createdAt" : ISODate("2020-01-04T20:57:14.634Z"),
        "__v" : 0
}
{
        "_id" : ObjectId("5e11ff8003eb832ef84342a6"),
        "isExpired" : false,
        "isBlocked" : false,
        "loginTentatives" : 0,
        "profile" : 2,
        "active" : true,
        "username" : "[email protected]",
        "password" : "$2a$10$wby3cs89jyO0HUbEiGLKye0jOB3U295zzIsu8xGJ4wnQtw5jcvSZO",
        "email" : "",
        "phone" : "",
        "createdAt" : ISODate("2020-01-05T15:23:44.386Z"),
        "__v" : 0
}
{
        "_id" : ObjectId("5e11ff9c03eb832ef84342a7"),
        "isExpired" : false,
        "isBlocked" : false,
        "loginTentatives" : 0,
        "profile" : 2,
        "active" : true,
        "username" : "[email protected]",
        "password" : "$2a$10$nEM3RxEjYbI77R9vOWUrMOGeHFDmdZqVKUNtTLuKZVLNQBQqIbew.",
        "email" : "",
        "phone" : "",
        "createdAt" : ISODate("2020-01-05T15:24:12.456Z"),
        "__v" : 0
}

Profiles

{
        "_id" : ObjectId("5e0ea5f6832df0473cacacda"),
        "number" : 1,
        "name" : "Cliente",
        "__v" : 0
}
{
        "_id" : ObjectId("5e0ea5ff832df0473cacacdb"),
        "number" : 2,
        "name" : "Restaurante",
        "__v" : 0
}
{
        "_id" : ObjectId("5e0ea607832df0473cacacdc"),
        "number" : 0,
        "name" : "Admin",
        "__v" : 0
}

And social medias:

{
        "_id" : ObjectId("5e1008943330ad05d4e1867c"),
        "name" : "Instagram",
        "__v" : 0
}
{
        "_id" : ObjectId("5e10089a3330ad05d4e1867d"),
        "name" : "Facebook",
        "__v" : 0
}
{
        "_id" : ObjectId("5e1009043330ad05d4e1867f"),
        "name" : "LinkedIn",
        "__v" : 0
}

My goal is to get all objects related to the restaurant objects. With the following code:

db.restaurants.aggregate([
{ $lookup: { from: "users", localField: "users._id", foreignField: "_id", as: "foundUsers" } }, 
{$group: { 
'_id': '$_id', 
'name': { "$first": "$name" }, 
'active': { "$first": "$active" }, 
users: { $push: '$foundUsers' }, 
branches: { "$first": "$branches" }, 
sections: { "$first": "$sections" },
socialMedias: { "$first": "$socialMedias" }
}
},
{$unwind: '$users'},
{ $unset: 'users.password' },
{ $lookup: { from: "profiles", localField: "users.profile", foreignField: "number", as: "profile" } },
{ $addFields: { 'users.profile': { $arrayElemAt: ['$profile', 0] } } },
{ $unset: 'profile' },
 

{ $lookup: { from: "socialmedias", localField: "socialMedias._id", foreignField: "_id", as: "socialMedia" } },
{ $addFields: { 'socialMedias.name': { $arrayElemAt: ['$socialMedia.name', 0] } } },
{$group: { 
'_id': '$_id', 
'name': { "$first": "$name" }, 
'active': { "$first": "$active" }, 
users: { $first: '$users' }, 
branches: { "$first": "$branches" }, 
sections: { "$first": "$sections" },
socialMedias: { "$first": "$socialMedias" }
}
}
])

I´m getting this:

    {
        "_id" : ObjectId("5e0ffd3d991918424c8d7c3c"),
        "name" : "Feijão de Corda",
        "active" : true,
        "users" : [ ],
        "branches" : [ ],
        "sections" : [ ],
        "socialMedias" : [ ]
}
{
        "_id" : ObjectId("5e0ffd23991918424c8d7c3b"),
        "name" : "Pizza Ruth",
        "active" : true,
        "users" : [ ],
        "branches" : [ ],
        "sections" : [ ],
        "socialMedias" : [ ]
}
{
        "_id" : ObjectId("5e0ff6d424f9fc12bc3d9464"),
        "name" : "Pizzaria Don Juan",
        "active" : true,
        "users" : [
                {
                        "_id" : ObjectId("5e10fc2adc147a373c312144"),
                        "isExpired" : false,
                        "isBlocked" : false,
                        "loginTentatives" : 0,
                        "profile" : {
                                "_id" : ObjectId("5e0ea5ff832df0473cacacdb"),
                                "number" : 2,
                                "name" : "Restaurante",
                                "__v" : 0
                        },
                        "active" : true,
                        "username" : "[email protected]",
                        "email" : "",
                        "phone" : "",
                        "createdAt" : ISODate("2020-01-04T20:57:14.634Z"),
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("5e11ff8003eb832ef84342a6"),
                        "isExpired" : false,
                        "isBlocked" : false,
                        "loginTentatives" : 0,
                        "profile" : {
                                "_id" : ObjectId("5e0ea5ff832df0473cacacdb"),
                                "number" : 2,
                                "name" : "Restaurante",
                                "__v" : 0
                        },
                        "active" : true,
                        "username" : "[email protected]",
                        "email" : "",
                        "phone" : "",
                        "createdAt" : ISODate("2020-01-05T15:23:44.386Z"),
                        "__v" : 0
                }
        ],
        "branches" : [
                {
                        "name" : "Teste"
                },
                {
                        "name" : "Teste 2"
                }
        ],
        "sections" : [
                {
                        "name" : "Bebidas"
                }
        ],
        "socialMedias" : [
                {
                        "_id" : ObjectId("5e1008943330ad05d4e1867c"),
                        "url" : "https://instagram/jetpizzas",
                        "name" : "Instagram"
                },
                {
                        "_id" : ObjectId("5e10089a3330ad05d4e1867d"),
                        "url" : "https://facebook.com/jetpizzas",
                        "name" : "Instagram"
                }
        ]
}

Note that the nested array socialMedias has a wrong value for the name of the social media (duplicated "Instagram" name, it should be one record for Instagram and another one for Facebook). Even if I try to unwind the socialMedias arrays from the restaurant collection, it returns only the restaurant object which has values of social medias as a result.

Any clue in how to make this right?

mickl · Accepted Answer · 2020-01-06 02:34:28Z

The way you merge $lookup results with existing array is an issue here. You cannot run:

{ $addFields: { 'socialMedias.name': { $arrayElemAt: ['$socialMedia.name', 0] } } },

because you'll always get first array element. You need to merge two arrays instead using $map , $filter and $mergeObjects:

{
    $addFields: {
        socialmedias: {
            $map: {
                input: "$socialMedias",
                as: "sm",
                in: {
                    $mergeObjects: [
                        "$$this",
                        {
                            $arrayElemAt: [ { $filter: { input: "$socialmedias", cond: { $eq: [ "$$sm.number", "$$this._id" ] } } }, 0 ]
                        }
                    ]
                }
            }
        }
    }
}

You also need to apply this for user.profile since current solution is error prone.

Mongo Playground

erickles · Accepted Answer · 2020-01-06 11:41:54Z

Thank you so much for the help. I tried this query:

db.restaurants.aggregate([
{ $lookup: { from: "users", localField: "users._id", foreignField: "_id", as: "foundUsers" } }, 
{$group: { 
'_id': '$_id', 
'name': { "$first": "$name" }, 
'active': { "$first": "$active" }, 
users: { $push: '$foundUsers' }, 
branches: { "$first": "$branches" }, 
sections: { "$first": "$sections" },
socialMedias: { "$first": "$socialMedias" }
}
},
{$unwind: '$users'},
{ $unset: 'users.password' },
{ $lookup: { from: "profiles", localField: "users.profile", foreignField: "number", as: "profile" } },
{ $addFields: { 'users.profile': { $arrayElemAt: ['$profile', 0] } } },
{ $unset: 'profile' },


{ $lookup: { from: "socialmedias", localField: "socialMedias._id", foreignField: "_id", as: "foundSocialMedia" } },
{
    $addFields: {
      socialMedias: {
        $map: {
          input: "$socialMedias",
          as: "sm",
          in: {
            $mergeObjects: [
              "$$sm",
              {
                $arrayElemAt: [
                  {
                    $filter: {
                      input: "$foundSocialMedia",
                      cond: {
                        $eq: [
                          "$$sm._id",
                          "$$this._id"
                        ]
                      }
                    }
                  },
                  0
                ]
              }
            ]
          }
        }
      }
    }
  },
{ $unset: 'foundSocialMedia' },
])

And I got this desired result:

{
        "_id" : ObjectId("5e0ffd3d991918424c8d7c3c"),
        "name" : "Feijão de Corda",
        "active" : true,
        "users" : [ ],
        "branches" : [ ],
        "sections" : [ ],
        "socialMedias" : [ ]
}
{
        "_id" : ObjectId("5e0ffd23991918424c8d7c3b"),
        "name" : "Pizza Ruth",
        "active" : true,
        "users" : [ ],
        "branches" : [ ],
        "sections" : [ ],
        "socialMedias" : [ ]
}
{
        "_id" : ObjectId("5e0ff6d424f9fc12bc3d9464"),
        "name" : "Pizzaria Don Juan",
        "active" : true,
        "users" : [
                {
                        "_id" : ObjectId("5e10fc2adc147a373c312144"),
                        "isExpired" : false,
                        "isBlocked" : false,
                        "loginTentatives" : 0,
                        "profile" : {
                                "_id" : ObjectId("5e0ea5ff832df0473cacacdb"),
                                "number" : 2,
                                "name" : "Restaurante",
                                "__v" : 0
                        },
                        "active" : true,
                        "username" : "[email protected]",
                        "email" : "",
                        "phone" : "",
                        "createdAt" : ISODate("2020-01-04T20:57:14.634Z"),
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("5e11ff8003eb832ef84342a6"),
                        "isExpired" : false,
                        "isBlocked" : false,
                        "loginTentatives" : 0,
                        "profile" : {
                                "_id" : ObjectId("5e0ea5ff832df0473cacacdb"),
                                "number" : 2,
                                "name" : "Restaurante",
                                "__v" : 0
                        },
                        "active" : true,
                        "username" : "[email protected]",
                        "email" : "",
                        "phone" : "",
                        "createdAt" : ISODate("2020-01-05T15:23:44.386Z"),
                        "__v" : 0
                }
        ],
        "branches" : [
                {
                        "name" : "Teste"
                },
                {
                        "name" : "Teste 2"
                }
        ],
        "sections" : [
                {
                        "name" : "Bebidas"
                }
        ],
        "socialMedias" : [
                {
                        "_id" : ObjectId("5e1008943330ad05d4e1867c"),
                        "url" : "https://instagram/jetpizzas",
                        "name" : "Instagram",
                        "__v" : 0
                },
                {
                        "_id" : ObjectId("5e10089a3330ad05d4e1867d"),
                        "url" : "https://facebook.com/jetpizzas",
                        "name" : "Facebook",
                        "__v" : 0
                }
        ]
}

Collectives™ on Stack Overflow

MongoDB aggregation with nested arrays of objects

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related