MongoDB nested lookup accross 3 collections with array

Question

I want to lookup from three collections. The starting point is a collection which has an array in which there are keys with which I want to lookup another collection. With the results from that lookup, I want to do another lookup. I'll try sharing a Mongo playground instance, for comfort.

This is the link to the instance: https://mongoplayground.net/p/4rJflL1UF81

Using this, you can see the mongodb version as well as the source collections, the query and the actual results.

I've already tried a few approaches:

db.SlittingPatterns.aggregate([
  {
    "$lookup": {
      "from": "Orders",
      "localField": "STPO.AUFNR",
      "foreignField": "OrderHead.AUFNR",
      "as": "ORDERS"
    }
  },
  {
    "$lookup": {
      "from": "Materials",
      "localField": "ORDERS.OrderHead.MATNR",
      "foreignField": "MATERIAL.AS_MARA.MATNR",
      "as": "MATERIALS"
    }
  }
])


db.SlittingPatterns.aggregate([
    {
        "$lookup": {
            "from": "Orders",
            "let": {
                "aufnr": "$STPO.AUFNR"
            },
            "pipeline": [
                {
                    "$match": {
                        "$expr": {
                            "$eq": [
                                "$OrderHead.AUFNR",
                                "$$aufnr"
                            ]
                        }
                    }
                },
                {
                    "$lookup": {
                        "from": "Materials",
                        "let": {
                            "matnr": "$OrderHead.MATNR"
                        },
                        "pipeline": [
                            {
                                "$match": {
                                    "$expr": {
                                        "$eq": [
                                            "$MATERIAL.AS_MARA.MATNR",
                                            "$$matnr"
                                        ]
                                    }
                                }
                            }
                        ],
                        "as": "MATERIALS"
                    }
                },
                {
                    "$unwind": "$MATERIALS"
                }
            ],
            "as": "ORDERS"
        }
    },
    {
        "$unwind": "$ORDERS"
    }
])

The last one was inspired from this question:

$lookup nested array in mongodb

Which seems pretty close to what I want, however it seems because the keys in the STPO array are inside objects, it doesn't work. If I switch

"let": {
  "aufnr": "$STPO.AUFNR"
}

with

"let": {
  "aufnr": "$STKO.RUNNR"
}

The desired format is being generated, but it's still not correct, because the AUFNRs need to be collected from the STPO array.

The first one fetches all the data I want, but not in the format I want, which will follow shortly. I propably just need to transform this result further inside my query, but at the moment, I can't figure out how. I also can't show what I already tried on that front, because I haven't gotten anywhere near close my desired format with that.

Here is the expected result:

[
  {
    "ORDERS": [
      {
        "MATERIALS": [
          {
            "MATERIAL": {
              "AS_MARA": {
                "MATNR": "456"
              }
            },
            "_id": ObjectId("5a934e000102030405000000")
          }
        ],
        "OrderHead": {
          "AUFNR": "123",
          "MATNR": "456"
        },
        "_id": ObjectId("5a934e000102030405000003")
      },
      {
        "MATERIALS": [
          {
            "MATERIAL": {
              "AS_MARA": {
                "MATNR": "654"
              }
            },
            "_id": ObjectId("5a934e000102030405000001")
          }
        ],
        "OrderHead": {
          "AUFNR": "321",
          "MATNR": "654"
        },
        "_id": ObjectId("5a934e000102030405000004")
      }
    ],
    "STKO": {
      "RUNNR": "123"
    },
    "STPO": [
      {
        "AUFNR": "123"
      },
      {
        "AUFNR": "321"
      }
    ],
    "_id": ObjectId("5a934e000102030405000005")
  }
]

score 1 · Accepted Answer · answered Aug 02 '19 at 14:48

We need to first unwind the 'STPO' to lookup its values into another collection. The following query can get you the expected output:

db.SlittingPatterns.aggregate([
    {
        $unwind:"$STPO"
    },
    {
        $lookup:{
            "from":"Orders",
            "let":{
                "aufnr":"$STPO.AUFNR"
            },
            "pipeline":[
                {
                    $match:{
                        $expr:{
                            $eq:["$OrderHead.AUFNR","$$aufnr"]
                        }
                    }
                },
                {
                    $lookup:{
                        "from":"Materials",
                        "let":{
                            "matnr":"$OrderHead.MATNR"
                        },
                        "pipeline":[
                            {
                                $match:{
                                    $expr:{
                                        $eq:["$MATERIAL.AS_MARA.MATNR","$$matnr"]
                                    }
                                }
                            }
                        ],
                        "as":"MATERIALS"
                    }
                }
            ],
            "as":"ORDERS"
        }
    },
    {
        $unwind:{
            path: "$ORDERS",
            "preserveNullAndEmptyArrays":true
        }
    },
    {
        $group:{
            "_id":"$_id",
            "STKO":{
                $first:"$STKO"
            },
            "STPO":{
                $push:"$STPO"
            },
            "ORDERS":{
                $push:"$ORDERS"
            }
        }
    }
]).pretty()

Output:

{
    "_id" : ObjectId("5d444745748b0f59369a8040"),
    "STKO" : {
        "RUNNR" : "123"
    },
    "STPO" : [
        {
            "AUFNR" : "123"
        },
        {
            "AUFNR" : "321"
        }
    ],
    "ORDERS" : [
        {
            "_id" : ObjectId("5d444732748b0f59369a803e"),
            "OrderHead" : {
                "AUFNR" : "123",
                "MATNR" : "456"
            },
            "MATERIALS" : [
                {
                    "_id" : ObjectId("5d444718748b0f59369a803b"),
                    "MATERIAL" : {
                        "AS_MARA" : {
                            "MATNR" : "456"
                        }
                    }
                }
            ]
        },
        {
            "_id" : ObjectId("5d444732748b0f59369a803f"),
            "OrderHead" : {
                "AUFNR" : "321",
                "MATNR" : "654"
            },
            "MATERIALS" : [
                {
                    "_id" : ObjectId("5d444718748b0f59369a803c"),
                    "MATERIAL" : {
                        "AS_MARA" : {
                            "MATNR" : "654"
                        }
                    }
                }
            ]
        }
    ]
}

MongoDB nested lookup accross 3 collections with array

1 Answers1