MongoDB - Aggregate two arrays by nesting object from one collection into array of objects of another collection

Question

I'm trying to properly write a query that will join objects from two collections. In one collection (A) I have objects with fields and array of users for example, each one of the user objects has the userDetailsId that I'm going to use when joining/merging each one of the users with each one of his userDetails from Collection B.

Collection A:

{
    "_id": "1",
    "field1": "value1",
    "field2": "value2",
    "users": [
        {
            "userField1": "value3",
            "userField2": "value4",
            "userDetailsId": "100"
        },{
            "userField1": "value5",
            "userField2": "value6",
            "userDetailsId": "200"
        }
    ]
}

Collection B:

{
    "_id": "100",
    "field1": "value1",
    "field2": "value2",
    "object1": {
        "field3": "value3",
        "field4": "value4"
    },
    "object2": {
        "field5": "value5",
        "field6": "value6"
    }
},
{
    "_id": "200",
    "field1": "value1",
    "field2": "value2",
    "object1": {
        "field3": "value3",
        "field4": "value4"
    },
    "object2": {
        "field5": "value5",
        "field6": "value6"
    }
}

Preferred result:

{
    "_id": "1",
    "field1": "value1",
    "field2": "value2",
    "users": [
        {
            "userField1": "value3",
            "userField2": "value4",
            "userDetailsId": "100",
            "userDetails":{
                "_id": "100",
                "field1": "value1",
                "field2": "value2",
                "object1": {
                    "field3": "value3",
                    "field4": "value4"
                },
                "object2": {
                    "field5": "value5",
                    "field6": "value6"
                }
            }
        },{
            "userField1": "value5",
            "userField2": "value6",
            "userDetailsId": "200",
            "userDetails":{
                "_id": "200",
                "field1": "value1",
                "field2": "value2",
                "object1": {
                    "field3": "value3",
                    "field4": "value4"
                },
                "object2": {
                    "field5": "value5",
                    "field6": "value6"
                }
            }
        }
    ]
}

One of the queries i have written but without getting preferred result:

db.collectiona.aggregate([{$match: {'_id':1}},{$lookup:{from:"collectionb", localField: "users.userdetailsid", foreignField: "_id", as:"userdetails"}}

If I run this I will just merge these two collections and will have all the matched userdetails objects from collection B inside the result as an array of userdetails.

Since my goal here is to merge users and their userdetails in user objects I then tried to put as: "users.userdetails" but that will replace user with userdetails data (haven't found a possible way to put upsert to true and add whole userdetails object in user when userdetailsid is matched).

After that I then tried to pipe the result of aggregation and tried to put userdetails inside user object with query

db.collectiona.aggregate([{$match: {'_id':1}},{$lookup:{from:"collectionb", localField: "users.userdetailsid", foreignField: "_id", as:"userdetails"},
{"$addFields":{
    "user.userdetails":{
        "$map":{
            "input": "$shifts",
            "in":{
                "$mergeObjects":["$$this",{"$arrayElemAt":["$collectionb",{"$indexOfArray":["$collectionb.id","$$this.userdetailsid"]}]}]
                }
            }
        }
    }}
}

But this was also not good.

Do you have any ideas how could I properly write query to get this kind of a result?

Ashh · Accepted Answer · 2018-08-17 09:41:30Z

2

You need to first $unwind the users array to match with the correct userDetailsId and then $group with $push to rollback users again into an array

db.collectiona.aggregate([
  { "$match": { "_id": 1 }},
  { "$unwind": "$users" },
  { "$lookup": {
    "from": "collectionb",
    "localField": "users.userDetailsId",
    "foreignField": "_id",
    "as": "users.userDetails"
  }},
  { "$unwind": "$users.userDetails" },
  { "$group": {
    "_id": "$_id",
    "field1": { "$first": "field1" }
    "field2": { "$first": "field2" }
    "users": { "$push": "users" }
  }}
])

answered Aug 17, 2018 at 9:41

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

2 Comments

calapurdito Over a year ago

Yes @AnthonyWinzlet this works, only need to be "field1": { "$first": "$field1" }. Want to ask you, in $group is there any ability to get all the fields from collectionA in one line or something or you need to state them field by field like "_id": "$_id", "field1": {"$first": "$field1" }, "field2": { "$first": "$field2" } , "field3": { "$first": "$field3" }. Asking that if collectionA has a lot of fields in the object besides users it would be fun to state them all. Much appreciate your answer, thanks.

Ashh Over a year ago

@calapurdito Great it works for you... and yes you have to mention each field with $first there is no other way to do so...

Collectives™ on Stack Overflow

MongoDB - Aggregate two arrays by nesting object from one collection into array of objects of another collection

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related