CODEWITHSUNDEEP
mongodbaggregation-frameworkaggregateaggregate-functionsaggregation
user12983086
user12983086

Reputation:

how to use lookup with a condition using MongoDB aggregation

have two collections with values

how to use lookup with a condition using MongoDB aggregation

first collection: basic_info

[
 {
    _id: "bmasndvhjbcw",
    name: "lucas",
    occupation: "scientist",
    present_working:true,
    age: 55,
    location: "texas",

  },
  {
    _id: "bmasndvhjbcx",
    name: "mark",
    occupation: "scientist",
    age: 45,
    present_working:true,
    location: "texas",
  },
  {
    _id: "bmasndvhjbcq",
    name: "cooper",
    occupation: "physicist",
    age: 69,
    location: "texas"
  }
]

second collection : test_results

[
 {
    basic_id: "bmasndvhjbcw",
    test_results:"PASS",

  },
  {
    basic_id: "bmasndvhjbcx",
    test_results:"PASS",
  },
  {
    basic_id: "bmasndvhjbcq",
    test_results:"FAIL",
  }
]

Lookup with condition where test_results: PASS and test_results: FAIL, should be excluded

expected_output after aggregation using lookup:

[
 {
    _id: "bmasndvhjbcw",
    name: "lucas",
    occupation: "scientist",
    present_working:true,
    age: 55,
    location: "texas",
    test_results:"PASS"

  },
  {
    _id: "bmasndvhjbcx",
    name: "mark",
    occupation: "scientist",
    age: 45,
    present_working:true,
    location: "texas",
    test_results:"PASS"
  }
]

MongoDB version: 4.0

Upvotes: 1

Views: 175

Answers (1)

Valijon
Valijon

Reputation: 13103

Since MongoDB v3.6, we can perform uncorrelated sub-queries with $lookup.

It returns list of matched results from test_results collection 0 ... N (in your case, it would be 0 ... 1). The next step, we filter empty test_results fields (non-matched documents).

In the last stage, we transform document into desired output result. $replaceRoot operator allows transform:

{                                      {
  ... other fields                       ... other fields
  test_results : [           ---->
    {test_results:"PASS"}    ---->       test_results:"PASS"
  ]
}                                      }

Try the query below:

db.basic_info.aggregate([
  {
    $lookup: {
      from: "test_results",
      let: {
        id: "$_id"
      },
      pipeline: [
        {
          $match: {
            test_results: "PASS",
            $expr: {
              $eq: [
                "$basic_id",
                "$$id"
              ]
            }
          }
        },
        {
          $project: {
            _id: 0,
            test_results: 1
          }
        }
      ],
      as: "test_results"
    }
  },
  {
    $match: {
      "test_results.0": {
        $exists: true
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          "$$ROOT",
          {
            $arrayElemAt: [
              "$test_results",
              0
            ]
          }
        ]
      }
    }
  }
])

MongoPlayground

Trick: Since you need only test_results:"PASS" from test_results collection, we can change $replaceRoot to $addFields:

{
  $addFields: {
    test_results: "PASS"
  }
}

Upvotes: 2

Related Questions