MongoDB aggregate lookup with multiple "ON" criteria

db.orders.aggregate([
  {
    "$match": {
      id: "123456789"
    }
  },
  {
    "$project": {
      items: {
        "$map": {
          "input": "$items",
          "as": "i",
          "in": {
            name: "$$i.name",
            externalCode: "$$i.externalCode"
          }
        }
      },
      options: {
        "$reduce": {
          "input": "$items",
          "initialValue": [],
          "in": {
            "$concatArrays": [
              "$$value",
              {
                "$reduce": {
                  "input": "$$this.options",
                  "initialValue": [],
                  "in": {
                    "$concatArrays": [
                      "$$value",
                      [
                        {
                          name: "$$this.name",
                          externalCode: "$$this.externalCode"
                        }
                      ]
                    ]
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    "$project": {
      union: {
        "$setUnion": [
          "$items",
          "$options"
        ]
      }
    }
  },
  {
    "$unwind": "$union"
  },
  {
    "$lookup": {
      "from": "stocks",
      let: {
        extCode: "$union.externalCode"
      },
      pipeline: [
        {
          $match: {
            merchantIds: "abc"
          }
        },
        {
          "$match": {
            $expr: {
              "$in": [
                "$$extCode",
                "$products.code"
              ]
            }
          }
        }
      ],
      "as": "lookupResult"
    }
  },
  {
    "$unwind": "$lookupResult"
  },
  {
    $project: {
      externalCode: "$union.externalCode",
      name: "$union.name"
    }
  }
])
Topics: MongoDB

Related Code Examples