MongoDB $lookup on multiple conditions: join with empty array field

Question

I have two collections,inf and data.

inf sample:

{
  "_id": {
    "$oid": "662bb5218bd38ee73cd579f0"
  },
  "point": 12,
  "code": "bc010",
  "v": [3160,3161,3163,3164],
  "str": ""
},
{
  "_id": {
    "$oid": "662bb5218bd38ee73cd579f1"
  },
  "point": 12,
  "code": "bc010",
  "v": [],
  "str": "*ai"
}
{
  "_id": {
    "$oid": "6665b853417e2f6485f72a6a"
  },
  "point": 14,
  "code": "bb050",
  "v": [],
  "str": ""
}

data sample:

{
  "_id": {
    "$oid": "6665b853417e2f6485f72a69"
  },
  "q": 3161,
  "point": 12,
  "code": "bc010",
  "str": "",
  "norm": "somedata"
},
{
  "_id": {
    "$oid": "662bb5218bd38ee73cd579f5"
  },
  "q": 3161,
  "point": 14,
  "code": "bb050",
  "str": "",
  "norm": "somemoredata"
}

I need to perform an aggregation from data to inf on the fields point, code, str and q. Is it possible to write a pipeline such that q is matched to the array field v in inf only if the array is not empty? I can get a pipeline to work with either the first three conditions or the last one, but not together. Here's an example of the pipeline for joining on q and v:

{
  from: "inf",
  localField: "point",
  foreignField: "point",
  let: {
    q: "$q",
    str: "$str"
  },
  pipeline: [
    {
      $match: {
        $expr: {
          $and: [
            {
              $in: ["$$q", "$v"]
            },
            {
              $eq: ["$str", "$$s"]
            }
          ]
        }
      }
    }
  ],
  as: "matches"
}

I'm quite new to MongoDB, so I apologize if I did not state the question properly. Any suggestion is very much appreciated.

Answer 1

You are actually on the right track. You just need to add a conditional "skip" in the data.q field with an $or.

db.data.aggregate([
  {
    "$lookup": {
      from: "inf",
      localField: "point",
      foreignField: "point",
      let: {
        q: "$q",
        str: "$str",
        code: "$code"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$$code",
                    "$code"
                  ]
                },
                {
                  $eq: [
                    "$str",
                    "$$str"
                  ]
                },
                {
                  $or: [
                    {
                      $eq: [
                        "$v",
                        []
                      ]
                    },
                    {
                      "$in": [
                        "$$q",
                        "$v"
                      ]
                    }
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "matches"
    }
  }
])

Mongo Playground