MongoDB nested lookup with 3 levels and 2 foreign keys at the last level-CodePudding

I have 3 collections namely recordSet, ecg, and exportingRegion. The 1st level has a foreign key with the 2nd level by source. As I checked with other topics, the 3rd level has only 1 foreign key with the 2nd level.

However, in this case, I have 2 foreign keys, which connect to the 1st and 2nd levels.

I would like to add more constraints when querying the last level, but I think it does not work. Can you help me check the query? any comment is highly appreciated.

Below here is my 3 collections with the data sample.

recordSet

{ "_id" : "1", "name" : "recordSet1", "source" : [1, 2, 3] }
{ "_id" : "2", "name" : "recordSet2", "source" : [1, 4, 5] }

ecg

{ "_id" : "1", "name_ecg" : "test1", "channel" : [ "I", "II", "III" ] }
{ "_id" : "2", "name_ecg" : "test2", "channel" : [ "II", "III" ] }
{ "_id" : "3", "name_ecg" : "test3", "channel" : [ "MLI", "MLII", "V5" ] }
{ "_id" : "4", "name_ecg" : "test4", "channel" : [ "I" ] }
{ "_id" : "5", "name_ecg" : "test5", "channel" : [ "II" ] }

exportingRegion

{ "_id" : "1", "name_exp_region" : "exp_reg1", "record_set_id" : "1", "ecg_id" : "1" }
{ "_id" : "2", "name_exp_region" : "exp_reg2", "record_set_id" : "1", "ecg_id" : "1" }
{ "_id" : "3", "name_exp_region" : "exp_reg3", "record_set_id" : "1", "ecg_id" : "2" }
{ "_id" : "4", "name_exp_region" : "exp_reg4", "record_set_id" : "1", "ecg_id" : "2" }
{ "_id" : "5", "name_exp_region" : "exp_reg5", "record_set_id" : "1", "ecg_id" : "2" }
{ "_id" : "6", "name_exp_region" : "exp_reg6", "record_set_id" : "1", "ecg_id" : "3" }
{ "_id" : "7", "name_exp_region" : "exp_reg7", "record_set_id" : "2", "ecg_id" : "1" }
{ "_id" : "8", "name_exp_region" : "exp_reg8", "record_set_id" : "2", "ecg_id" : "1" }
{ "_id" : "9", "name_exp_region" : "exp_reg9", "record_set_id" : "2", "ecg_id" : "1" }

Here is my query:

db.recordSet.aggregate(
    [
        {
            '$match': {
                '_id': 1
            }
        }, {
            '$lookup': {
                'from': 'ecg', 
                'localField': 'source', 
                'foreignField': '_id', 
                'as': 'ecg'
            }
        }, {
            '$unwind': {
                'path': '$ecg', 
                'preserveNullAndEmptyArrays': True
            }
        }, {
            '$lookup': {
                'from': 'exportingRegion', 
                'localField': 'ecg._id', 
                'foreignField': 'ecg_id', 
                'as': 'ecg.exportingRegion'
            }
        }, {
            '$lookup': {
                'from': 'exportingRegion', 
                'localField': 'record_set_id', 
                'foreignField': '_id', 
                'as': 'record_set.exportingRegion'
            }
        }, {
            '$group': {
                '_id': '$_id', 
                'ecg': {
                    '$push': {
                        'ecg': '$ecg', 
                        'exporting_region': '$exportingRegion'
                    }
                }
            }
        }
    ]
)

Expectation:

{
  "_id": "1",
  "ecg": [
    {
      "_id": "1",
      "name_ecg": "test1",
      "channel": [
        "I",
        "II",
        "III"
      ],
      "exportingRegion": [
        {
          "_id": "1",
          "name_exp_region": "exp_reg1"
        },
        {
          "_id": "2",
          "name_exp_region": "exp_reg2"
        }
      ]
    },
    {
      "_id": "2",
      "name_ecg": "test2",
      "channel": [
        "II",
        "III"
      ],
      "exportingRegion": [
        {
          "_id": "3",
          "name_exp_region": "exp_reg3"
        },
        {
          "_id": "4",
          "name_exp_region": "exp_reg4"
        },
        {
          "_id": "5",
          "name_exp_region": "exp_reg5"
        }
      ]
    },
    {
      "_id": "3",
      "name_ecg": "test3",
      "channel": [
        "MLI",
        "MLII",
        "V5"
      ],
      "exportingRegion": [
        {
          "_id": "6",
          "name_exp_region": "exp_reg6"
        }
      ]
    }
  ]
}

CodePudding user response：

One option is using the $lookup pipeline to enforce your limitations:

db.recordSet.aggregate([
  {$match: {_id: "1"}},
  {$lookup: {
      from: "ecg",
      localField: "source",
      foreignField: "_id",
      as: "ecg"
    }
  },
  {$unwind: {path: "$ecg", preserveNullAndEmptyArrays: true}},
  {$lookup: {
      from: "exportingRegion",
      let: {recordId: "$_id", ecgId: "$ecg._id"},
      pipeline: [
        {$match: {
            $expr: {
              $and: [
                {$eq: ["$ecg_id", "$$ecgId"]},
                {$eq: ["$record_set_id", "$$recordId"]}
              ]
            }
          }
        }
      ],
      as: "exportingRegion"
    }
  },
  {$group: {
      _id: "$_id",
      ecg: {$push: {ecg: "$ecg", exporting_region: "$exportingRegion"}}
    }
  }
])

See how it works on the playground example