CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
nnson1610
nnson1610

Reputation: 117

Aggregate, lookup from 2 collections and extract the result

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

[
    {
        _id: 1,
        testCaseId: 'x',
        testScriptId: 1,
        createById: 1
    },
    {
        _id: 2,
        testCaseId: 'x',
        testScriptId: 2,
        createById: 2
    }
]

and another collection is "testCaseCollection":

[
    {
        _id: 1,
        testCaseId: x,
        testScripts: [
            {
                testScriptId: 1,
                testScriptName: 'testScript1_Name'
            },
            {
                testScriptId: 2,
                testScriptName: 'testScript2_Name'
            }
        ]
    }
]

and the last collection is "membersCollection"

[
    {
        _id: 1,
        userName: 'John'
    },
    {
        _id: 2,
        userName: 'Mark'
    }
]

I need to extract the result that lookup each records on "testScriptResultCollection" from "testCaseCollection" (by testCaseId and testScriptId to get its testScriptName) and "membersCollection"(by userId to get its userName

The result I want is sth like: 

 [ 
        {
            _id: 1,
            testCaseId: 'x',
            testScriptId: 1,
            createById: 1,
            testScriptName: 'testScript1_Name',
            userName: 'John'
        },
        {
            _id: 2,
            testCaseId: 'x',
            testScriptId: 2,
            createById: 2,
            testScriptName: 'testScript2_Name',
            userName: 'Mark'
        },
    ]

I had tried a query like the link below, but it is not the best way to do that. https://mongoplayground.net/p/dGdPGV3GEQn

Can annyone help me? Thanks so much.

Upvotes: 2

Views: 90

Answers (1)

Ashh
Ashh

Reputation: 46451

You can use below optimized aggregation pipeline

db.testScriptResultCollection.aggregate([
  { "$match": { "testCaseId": "x" }},
  { "$lookup": {
    "from": "testCaseCollection",
    "let": { "testScriptId": "$testScriptId" },
    "pipeline": [
      { "$match": { "$expr": { "$in": ["$$testScriptId", "$testScripts.testScriptId"] }}},
      { "$unwind": "$testScripts" },
      { "$match": { "$expr": { "$eq": ["$$testScriptId", "$testScripts.testScriptId"] }}},
      { "$project": { "testScripts": 1, "_id": 0 }}
    ],
    "as": "tr"
  }},
  { "$lookup": {
    "from": "membersCollection",
    "let": { "createById": "$createById" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$$createById", "$_id"] }}}
    ],
    "as": "user"
  }},
  { "$addFields": {
    "testScriptName": { "$arrayElemAt": [ "$tr.testScripts.testScriptName", 0 ] },
    "userName": { "$arrayElemAt": ["$user.userName", 0] }
  }},
  { "$project": { 'user': 0, "tr": 0 }}
])

Upvotes: 2

Related Questions