CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
nnson1610
nnson1610

Reputation: 117

Aggregate, lookup and addField from nested array

I have a trouble with MongoDB query. First, I have a "testScriptResultCollection" with structure below:

{
    testCaseId: x
    testScriptId: 1
    descripttion: aaa

}
{
    _id: 2
    testCaseId: x
    testScriptId: 2
    descripttion: bbb
}
{
    _id: 3
    testCaseId: x
    testScriptId: 3
    descripttion: ccc
}

and another collection is "testCaseCollection":

{
    _id: 1
    testCaseId: x
    testScripts: [
        {
            testScriptId: 1
            name: testScript1_Name
        },
        {
            testScriptId: 2
            name: testScript2_Name
        }
        {
            testScriptId: 3
            name: testScript3_Name
        }
    ]
}

I need to extract an object like:

 [ 
        {
            testCaseId: x
            testScriptId: 1
            descripttion: aaa
            name: testScript1_Name
        },
        {
            testCaseId: x
            testScriptId: 2
            descripttion: bbb
            name: testScript2_Name
        },
        {
            testCaseId: x
            testScriptId: 3
            descripttion: ccc
            name: testScript3_Name
        },
    ]

I had tried a query to lookup 2 collections with "testCaseId" and find the "name" of testScriptId like that but it goes wrong

testScriptResultCollection.aggregate{[
    {
        $match: {testCaseId : x}
    },
    {
            $lookup:
            {
                from: "testCaseCollection"
                localField: "testCaseId",
                foreignField: "testCaseId",
                as: "combineResults"
            }
    },
    {
        $addFields : 
        {
            "name": {
                $filter: { 
                    input: "$combineResults.testScripts",
                    as: "testScriptArr",
                    cond: { $eq: ["$$testScriptArr.testScriptId", $testScriptId]}
                }
            }
        }
    }
]}

Can somebody help me. Any help would be appreciated. Thanks a lot.

Upvotes: 1

Views: 4447

Answers (1)

Ashh
Ashh

Reputation: 46481

You can try below aggregation

Basically you need to use $mergeObjects to merge both the collection objects and finally $replaceRoot to shift them to the top level.

db.getCollection('testCaseCollection').aggregate([
  { "$unwind": "$testScripts" },
  { "$lookup": {
    "from": "testScriptResultCollection",
    "localField": "testScripts.testScriptId",
    "foreignField": "testScriptId",
    "as": "newField"
  }},
  { "$unwind": "$newField" },
  { "$replaceRoot": { "newRoot": { "$mergeObjects": ["$newField", "$testScripts"] }}}
])

Upvotes: 2

Related Questions