Mongodb Aggregate : replace value of one collection with matching value of other collection

Question

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

1st collection name is a

db.a.find()

{
"_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

db.b.find()

 {
    "_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"]
    }
] }

Requirement: fields inside each document of versions are not fixed, Example : versions[0] have 4 key-value pair and versions[1] have 6 key-value pair. so I am looking a query which can replace owner_id with email keeping all other filed in output.

I tried :

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()

Please help.

Thank You!!!

Please post what have you tried

Saravana
– Saravana

2018-01-10 08:27:40 +00:00
Commented Jan 10, 2018 at 8:27 — Saravana
– Saravana, Commented Jan 10, 2018 at 8:27
I edited the post, added what I tried. :)

Amit Kumar
– Amit Kumar

2018-01-10 08:32:21 +00:00
Commented Jan 10, 2018 at 8:32 — Amit Kumar
– Amit Kumar, Commented Jan 10, 2018 at 8:32

Neodan · Accepted Answer · 2018-01-10 09:55:47Z

3

Instead of $project pipeline stage use $addFields.

Example:

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

answered Jan 10, 2018 at 9:55

Neodan

5,2742 gold badges31 silver badges39 bronze badges

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

Collectives™ on Stack Overflow

Mongodb Aggregate : replace value of one collection with matching value of other collection

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related