I have two collections in MongoDB with some arrays and nested arrays inside and I would like to lookup or something else considering two foreign keys.

Collection structure:

Stocks

{
 merchantIds: ["abc"],
 products: [
  {
   code: "123",
   quantity: 10
  },
  {
   code: "456",
   quantity: 15
  },
  {
   code: "999",
   quantity: 99
  }
 ]
}

Orders

{
 id: "123456789",
 items: [
  {
   name: "aaa",
   externalCode: "123",
   quantity: 1,
   options: [
    {
     name: "ccc",
     externalCode: "999",
     quantity: 2,
    }
   ],
  },
  {
   name: "bbb",
   externalCode: "456",
   quantity: 2,
   options: [
    name: "aaa",
    externalCode: "123",
    quantity: 5,
   ]
  },
  {
   name: "ddd",
   externalCode: "789",
   quantity: 2,
   options: []
  }
 ]
}

I would like to create an aggregation pipeline between both collections to get this result:

[
 {
  name: "aaa",
  externalCode: "123"
 },
 {
  name: "bbb",
  externalCode: "456"
 },
 {
  name: "ccc",
  externalCode: "999"
 }
]

How could I consider both items.externalCode and items.options.externalCode in an aggregate pipeline and reduce to this result?

CodePudding user response：

You can do the followings in an aggregation pipeline:

1. flatten the items and items.options array by using $map and $reduce
2. create a union array of the results above using $setUnion
3. $lookup to the stocks collection. In the sub-pipeline, specify your join criteria
4. $unwind to prune the unmatched result

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"
    }
  }
])

Here is the Mongo playground for your reference.