Mongodb Aggregate JSON array field for the matching field of other collection

Question

I am new to mongodb , I have two collections like this :

1st collection name is A

{
"_id": "1234",
"versions": [{

        "owner_id": ObjectId("100000"),
        "versions": 1,
        "type" : "info",
        "items" : ["item1","item3","item7"]


    },
    {

        "owner_id": ObjectId("100001"),
        "versions": 2,
        "type" : "bug",
        "OS": "Ubuntu",
        "Dependencies" : "Trim",
        "items" : ["item1","item7"]

    }
]}

2nd Collection name is B

{ "_id": ObjectId("100000"), "email": "[email protected]" } { "_id": ObjectId("100001"), "email": "[email protected]"}

Expected output is :

{
"_id": "1234",
"versions":[{

        "owner_id": "[email protected]",
        "versions": 1,
        "type" : "info",
        "items" : ["item1","item3","item7"]


    },
    {

        "owner_id": "[email protected]",
        "versions": 2,
        "type" : "bug",
        "OS": "Ubuntu",
        "Dependencies" : "Trim",
        "items" : ["item1","item7"]

    }
] }

I used mongo $lookup but I am not getting required output Please help.

Thank You!!!

I tried with this query db.A.aggregate([{ "$lookup": { "from": "B", "localField": "versions.owner_id", "foreignField": "_id", "as": "owner_id" } }, { "$unwind": "$owner_id" }]).pretty() — Amit Kumar
– Amit Kumar, Commented Jan 9, 2018 at 11:29
Please use the edit link to update your question content with the pipeline, code in comments are discouraged here on SO. — chridam
– chridam, Commented Jan 9, 2018 at 12:41
I have requested to deleted this question and posted with more query info ... Link is here — Amit Kumar
– Amit Kumar, Commented Jan 10, 2018 at 8:09
No need to delete the question, just use the edit link to update the question with the details. — chridam
– chridam, Commented Jan 10, 2018 at 9:18

Saravana · Accepted Answer · 2018-01-09 12:45:43Z

2

You need to $unwind versions, $lookup with another collection on foreignField, $project to take the first element from the match array, $group to get back in original document format

collection a

> db.a.find()
{ "_id" : "1234", "versions" : [ { "owner_id" : "100000" }, { "owner_id" : "100001" }, { "owner_id" : "100001" } ] }

collection b

> db.b.find()
{ "_id" : "100000", "email" : "[email protected]" }
{ "_id" : "100001", "email" : "[email protected]" }

aggregate pipeline

> db.a.aggregate(
        [
            {$unwind:"$versions"},
            {$lookup : {from : "b", "localField":"versions.owner_id", "foreignField":"_id", as :"out"}}, 
            {$project : {"_id":1, "versions.owner_id":{$arrayElemAt:["$out.email",0]}}},
            {$group:{_id:"$_id", versions : {$push : "$versions"}}}
        ]   
    ).pretty()

output

{
        "_id" : "1234",
        "versions" : [
                {
                        "owner_id" : "[email protected]"
                },
                {
                        "owner_id" : "[email protected]"
                },
                {
                        "owner_id" : "[email protected]"
                }
        ]
}

answered Jan 9, 2018 at 12:45

Saravana

12.8k2 gold badges42 silver badges60 bronze badges

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

2 Comments

Amit Kumar Over a year ago

I have deleted this question and posted with more query info ... Link is here

Saravana Over a year ago

You don't need to delete, this may be helpful for someone who's looking for a similar issue

Collectives™ on Stack Overflow

Mongodb Aggregate JSON array field for the matching field of other collection

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related