MongoDB $lookup with conditional foreignField

Question

Playground: https://mongoplayground.net/p/OxMnsCFZpmQ

My MongoDB version: 4.2.

I have a collection car_parts and customers. As the name suggests car_parts has car parts, where some of them can have a field sub_parts which is a list of car_parts._ids this part consists of.

Every customer that bought something at us is stored in customers. The parts field for a customer contains a list of parts the customer bought together on a certain date.

I would like to have an aggregate query in MongoDB that returns a mapping of which car parts were bought (bought_parts) from which customers. However, if the car_parts has the field sub_parts, the customer should show up for the subparts only.

So the query in the playground gives almost the correct result already, except for the sub_parts topic.

Example for customer_3:

{
      "_id": "customer_3",
      "parts": [
        {
          "bought_parts": [
            3
          ],
          date: "15.07.2020"
        }
      ]
   }

Since bought_parts has car_parts._id = 3:

{
      "_id": 3,
      "name": "steering wheel",
      "sub_parts": [
        1, // other car_parts._id s
        2
      ]
 }

The result should show customer_3 as a customer of car parts 1 and 2. I'm not sure how to accomplish this, but I assume a "temporary" replacement of the id 3 in bought_parts with the actual ids [1,2] might solve it.

Expected output:

[
  {
    "_id": 1,
    "customers": [
      "customer_1",
      "customer_2",
      "customer_3"  // <- since customer_3 bought car part 3 which has 1 in sub_parts
    ]
  },
  {
    "_id": 2,
    "customers": [
     "customer_3"  // <- since customer_3 bought car part 3 which has 2 in sub_parts
    ]
  },
  {
    "_id": 3,
    "customers": [
      "customer_1", // <- since car_parts.id = 3 has [1, 2] in sub_parts, show customers of ids [1, 2]
      "customer_2",
      "customer_3"
    ]
  },
  {
    "_id": 4,
    "customers": [
      "customer_1",
      "customer_2"
    ]
  }
]

Thanks a lot in advance!

Answer 1

EDIT: One way to do it is:

db.car_parts.aggregate([
  {
    $project: {
      topLevel: {$concatArrays: [{$ifNull: ["$sub_parts", []]}, ["$_id"]]},
      sub_parts: 1
    }
  },
  {$unwind: "$topLevel"},
  {
    $group: {
      _id: "$topLevel",
      parts: {$push: "$_id"},
      sub_parts: {$first: "$sub_parts"}
    }
  },
  {
    $project: {
      parts: {$concatArrays: [{"$ifNull": ["$sub_parts", []]}, "$parts"]}
    }
  },
  {
    $lookup: {
      from: "customers",
      localField: "parts",
      foreignField: "parts.scanned_parts",
      as: "customers"
    }
  },
  {$project: {customers: "$customers._id"}}
])

As you can see working on this playground.

Since you said there is only one level of sub-parts, I used another idea: creating a top level before the $lookup. Since you want customers that used part 3 for example, to be registered under parts 1,2 which are sub-parts of 3, the idea is to group them. This connection is a bit clumsy after the $lookup, but if we use the data that we have on the car_parts collection before the $lookup, we actually knows already that parts 1,2 are subpart of 3. Creating a topLevel temporary field, allows to group, in advance, all the parts and sub-parts that if a customer used on of them, he should be registered under this top level part. This makes things much more elegant...