Changing foreignField with pipeline not working

[Bogi_Niczuly]

Hi,

I have a collection Members and I am trying to make a join with the same collection to find all the patients of a staff member.

I have the following members:

[
  {
    "_id": "alice",
    "type": "staff"
  },
  {
    "_id": "john",
    "type": "staff"
  },
  {
    "id": "janice",
    "type": "patient",
    "careTeam": [
      {
        "_id": "alice",
        "primary": true
      }
    ]
  },
  {
    "id": "michael",
    "type": "patient",
    "careTeam": [
      {
        "_id": "john",
        "primary": true
      },
      {
        "_id": "alice"
      }
    ]
  }
]

The issue is I need to add two conditions but I can’t. I need careTeam._id = $$mainId and careTeam.primary=true

I only want to receive the primary caseload of the staff not the whole caseload

If I do a lookup like this I receive both janice and michael but I only need janice

{
  $lookup: {
    from: "Members",
    localField: "_id",
    foreignField: "careTeam._id",
    as: "caseLoad"
 }
}

I tried

{
  $lookup: {
    from: "Members",
    let: { primary: "_id" },
    pipeline: [{$unwind:"$careTeam"}, 
              {
                $match: {
                  $expr: {
                    $and: [{ $eq: ["$careTeam._id","$$primary"] }],
                  },
                },
              }],
  }
}

but nothing I get empty caseLoad, I also tried with $unwind but still nothing.

{
  $lookup: {
    from: "Members",
    let: { primary: "_id" },
    pipeline: [{$unwind:"$careTeam"}, 
              {
                $match: {
                  $expr: {
                    $and: [{ $eq: ["$careTeam._id","$$primary"] }],
                  },
                },
              }],
  }
}

I know I still have to add { $eq: ["$careTeam.primary", true] } inside the mathc expr but first I need to receive back something in the caseLoad.

Can anyone help me out in what am I doing wrong?

Thank you for your help in advance,
Bogi

[Takis]

Hello : )

If you need for all staff,their primary patients,look up can do it.
If you need for a specific staff,it could be much more simple without look up.
Maybe there is a better way in general but this does what you want i think.

Mongodb has also $graphLookup but here is only 1 level the relation,so lookup works also.
If you have 1 relation(like reports to) ,and many connections look that also.

$graphLookup (aggregation) — MongoDB Manual

Data in (added george also)

[
  {
    "_id": "alice",
    "type": "staff"
  },
  {
    "_id": "john",
    "type": "staff"
  },
  {
    "_id": "janice",
    "type": "patient",
    "careTeam": [
      {
        "_id": "alice",
        "primary": true
      },
      {
        "_id": "john"
      }
    ]
  },
  {
    "_id": "george",
    "type": "patient",
    "careTeam": [
      {
        "_id": "alice",
        "primary": true
      },
      {
        "_id": "john"
      }
    ]
  },
  {
    "_id": "michael",
    "type": "patient",
    "careTeam": [
      {
        "_id": "john",
        "primary": true
      },
      {
        "_id": "alice"
      }
    ]
  }
]

Query
Left aggregation = keep the staff only
Right aggregation = keep the patients,from the careTeam,keep only the names of the primary care takers(reduce),join only is the staff id is on the array of the primary caretakers,and keep
only the id of that patient.

{
  "aggregate": "testcoll",
  "pipeline": [
    {
      "$match": {
        "$expr": {
          "$eq": [
            "$type",
            "staff"
          ]
        }
      }
    },
    {
      "$lookup": {
        "from": "testcoll",
        "let": {
          "staffid": "$_id"
        },
        "pipeline": [
          {
            "$match": {
              "$expr": {
                "$eq": [
                  "$type",
                  "patient"
                ]
              }
            }
          },
          {
            "$addFields": {
              "primaryCareTeam": {
                "$reduce": {
                  "input": "$careTeam",
                  "initialValue": [],
                  "in": {
                    "$cond": [
                      {
                        "$eq": [
                          "$$this.primary",
                          true
                        ]
                      },
                      {
                        "$concatArrays": [
                          "$$value",
                          [
                            "$$this._id"
                          ]
                        ]
                      },
                      "$$value"
                    ]
                  }
                }
              }
            }
          },
          {
            "$match": {
              "$expr": {
                "$in": [
                  "$$staffid",
                  "$primaryCareTeam"
                ]
              }
            }
          },
          {
            "$project": {
              "_id": 1
            }
          }
        ],
        "as": "primaryPatients"
      }
    }
  ],
  "maxTimeMS": 1200000,
  "cursor": {}
}

Result

{
    "_id": "alice",
    "type": "staff",
    "primaryPatients": [
      {
        "_id": "janice"
      },
      {
        "_id": "george"
      }
    ]
  },
  {
    "_id": "john",
    "type": "staff",
    "primaryPatients": [
      {
        "_id": "michael"
      }
    ]
  }

[Bogi_Niczuly]

Hi Takis,

Thank you for your answer I will try it out, although I think it still should be a more optimal solution for this, something with matching on the first pipeline state.

Do you know why this version is not working?

     {
      $lookup: {
    from: "Members",
    let: { primary: "_id" },
    pipeline: [ 
              {
                $match: {
                  $expr: {
                    $and: [{ $eq: ["$careTeam._id","$$primary"] }],
                  },
                },
              }],
      }
    }
``

Is that because the careTeam is an array? But the localField, foreignField solution is working.

Bogi

[Takis]

Hello

Yes $careTeam._id is an array,of all the _ids in careTeam array.
The above code assumes that maybe they are many primary careTakers.
Thats why i did reduce (keep only the primary) and then $in.
If there is only one primary careTake you can do filter ,then get first element and $eq.

But if each patient have only 1 primary,i guess one extra field,primary_caretaker would be better,and all the secondaries in the array.
And then you can use the $eq without filter or reduce needed.