Aggregate unwind and lookup query replacing the data instead of appending

Hello there,
I have a collection reports as follows:

{
    "_id": { "$oid": "5f05e1d13e0f6637739e215b" },
    "testReport": [
      {
        "name": "Calcium",
        "value": "87",
        "slug": "ca",
        "details": {
          "description": "description....",
          "recommendation": "recommendation....",
          "isNormal": false
        }
      },
      {
        "name": "Magnesium",
        "value": "-98",
        "slug": "mg",
        "details": {
          "description": "description....",
          "recommendation": "recommendation....",
          "isNormal": false
        }
      }
    ],
"anotherTestReport": [
        {
          "name": "Calcium",
          "value": "-60",
          "slug": "ca",
          "details": {
            "description": "description....",
            "recommendation": "recommendation....",
            "isNormal": false
          }
        },
        {
          "name": "Magnesium",
          "value": "80",
          "slug": "mg",
          "details": {
            "description": "description....",
            "recommendation": "recommendation....",
            "isNormal": false
          }
        }
      ],
    "patientName": "Patient Name",
    "clinicName": "Clinic",
    "gender": "Male",
    "bloodGroup": "A",
    "createdAt": { "$date": "2020-07-08T15:10:09.612Z" },
    "updatedAt": { "$date": "2020-07-08T15:10:09.612Z" }
  }

and another collection setups

{
    "_id": { "$oid": "5efcba7503f4693d164e651d" },
    "code": "Ca",
    "codeLower": "ca",
    "name": "Calcium",
    "valueFrom": -75,
    "valueTo": -51,
    "treatmentDescription": "description...",
    "isNormal": false,
    "gender": "",
    "recommendation": "recommendation...",
    "createdAt": { "$date": "2020-07-01T16:31:50.205Z" },
    "updatedAt": { "$date": "2020-07-01T16:31:50.205Z" }
  },
  {
    "_id": { "$oid": "5efcba7503f4693d164e651e" },
    "code": "Ca",
    "codeLower": "ca",
    "name": "Calcium",
    "valueFrom": 76,
    "valueTo": 100,
    "treatmentDescription": "description...",
    "isNormal": false,
    "gender": "",
    "recommendation": "recommendation...",
    "createdAt": { "$date": "2020-07-01T16:31:50.205Z" },
    "updatedAt": { "$date": "2020-07-01T16:31:50.205Z" }
  },
  {
    "_id": { "$oid": "5efcba7603f4693d164e65bb" }, 
    "code": "Mg",
    "codeLower": "mg",
    "name": "Magnesium",
    "valueFrom": -100,
    "valueTo": -76,
    "treatmentDescription": "description...",
    "isNormal": false,
    "gender": "",
    "recommendation": "recommendation...",
    "createdAt": { "$date": "2020-07-01T16:31:50.205Z" },
    "updatedAt": { "$date": "2020-07-01T16:31:50.205Z" }
  },
  {
    "_id": { "$oid": "5efcba7503f4693d164e6550" },
    "code": "Mg",
    "codeLower": "mg",
    "name": "Magnesium",
    "valueFrom": 76,
    "valueTo": 100,
    "treatmentDescription": "description...",
    "isNormal": false,
    "gender": "",
    "recommendation": "recommendation...",
    "createdAt": { "$date": "2020-07-01T16:31:50.205Z" },
    "updatedAt": { "$date": "2020-07-01T16:31:50.205Z" }
  }

I wanted to search the value from reports collection and check whether the value is in range from the setups collection and return the _id and add the returned _ids in setupIds field on reports collection.

I tried with the following query:

db.reports.aggregate([
  {
    $match: {
      _id: ObjectId("5f05e1d13e0f6637739e215b")
    }
  },
  {
    $unwind: {
      path: "$testReport"
    }
  },
  {
    $lookup: {
      from: "setup",
      "let": {
        testValue: {
          $toInt: "$testReport.value"
        },
        testName: "$testReport.name",
      },
      pipeline: [
        {
          $match: {
            $expr: {
             $and:  [{
                            "$eq": [
                                "$name",
                                "$$testName"
                            ]
                        },
                        {
                            "$gte": [
                                "$valueTo",
                                "$$testValue"
                            ]
                        },
                        {
                            "$lte": [
                                "$valueFrom",
                                "$$testValue"
                            ]
                        }
                    ]
            }
          }
        }
      ],
      as: "setupIds"
    }
  },
  {
    $group: {
      _id: "$_id",
      patientName: {
        $first: "$patientName"
      },
      clinicName: {
        $first: "$clinicName"
      },
      gender: {
        $first: "$gender"
      },
      bloodGroup: {
        $first: "$bloodGroup"
      },
      createdAt: {
        $first: "$createdAt"
      },
      updatedAt: {
        $first: "$updatedAt"
      },
      setupIds: {
        $addToSet: "$setupIds._id"
      }
    }
  },
  {
    $addFields: {
      setupIds: {
        $reduce: {
          input: "$setupIds",
          initialValue: [],
          "in": {
            $setUnion: [
              "$$this",
              "$$value"
            ]
          }
        }
      }
    }
  }
{ $merge: { into: "updatedReports" } },
])

It’s working as expected. A new collection is added with a field setupIds. I again tried to run the same query but replacing the testReport with anotherTestReport on $unwind and $lookup, hoping new ids will be appended in setupIds. Instead of appending, it replaced the previous ids.

Is there any way that the new values will be appended?

Thanks.

Welcome to the forum, @tushark !

Let me simplify your case and provide you some example, so it would be easier for me to explain and for you - to understand.

Assume, you have this dataset:

db.teams.insertMany([
  { _id: 'T1', country: 'France' },
  { _id: 'T2', country: 'Spain' },
]);

And your plain is to:

  1. Match teams one-by-one
  2. Join players from other collection
  3. Collect those players ids into array and put it into some playersIds field.
  4. Output team’s _id and playerdsIds fields into another collection.

Here is how it can be achieved with an aggregation:

db.teams.aggregate([
  {
    // match part of existing documents
    $match: {
      _id: 'T1'
    },
  },
  {
    // calculate ids of players somehow
    $project: {
      _id: null,
      playersIds: ['P1', 'P2']
    }
  },
  {
    $merge: {
      into: 'output',
      whenMatched: [
        // use this pipeline to define merging behaviour
        {
          $addFields: {
            differentIds: {
              // concat current values with new ones
              $concatArrays: [
                '$playersIds',
                {
                  // detect which values are new
                  $setDifference: ['$$new.playersIds', '$playersIds']
                }
              ],
            }
          }
        }
      ]
    }
  }
]);

The output will be:

{ "_id" : null, "playersIds" : [ "P1", "P2" ] }

Then, if you match another team (let’s say “T2” team) and join it’s players (let’s assume their ids are: P3, P4), the document in the output collection will be updated and will look like this:

{ "_id" : null, "playersIds" : [ "P1", "P2", "P3", "P4" ] }