Perform lookup on all dynamic reference fields if they exist

Viewed 161

I have a rather complicated situation that I want to solve. I have a very dynamic model that allows to reference other objects (in the same collection). The reference may be a single value or an array of references. Also, the names are not known in advance (in this example i named them ref1 and ref2 but they may be anything). What identifies the reference object is that a field with key referenceId exists. I actually did resolve that part (see further below). Sample data would look like this (I chose strings instead of ObjectIds for the sake of simplicity):

[
  {
    "id": "1",
    "name": "First"
  },
  {
    "id": "2",
    "name": "Second",
    "ref1": {
      "referenceId": "3"
    },
    "ref2": [
      {
        "referenceId": "1",
      },
      {
        "referenceId": "3"
      }
    ]
  },
  {
    "id": "3"
    "name": "Third",
  }
]

The desired result would be (after the lookups have been performed and the resulting objects replaced inline) - here for the object with id = 2:

[
  {
    "id": "2",
    "name": "Second",
    "ref1":   {
      "id": "1",
      "name": "First"
    },
    "ref2": [
      {
        "id": "1",
        "name": "First"
      },
      {
        "id": "3"
        "name": "Third",
      }
    ]
  }
]

After a lot of experimenting I came up with this idea for an aggregation pipeline which produces the following output:

[
  {
    "id": "2",
    "name": "Second",
    "ref1": {
      "id": "3",
      "name": "Third"
    },
    "ref2": [
      {
        "referenceId": "1"
      },
      {
        "referenceId": "3"
      }
    ]
  },
  {
    "id": "2",
    "name": "Second",
    "ref1": {
      "referenceId": "3"
    },
    "ref2": {
      "id": "1",
      "name": "First"
    }
  },
  {
    "id": "2",
    "name": "Second",
    "ref1": {
      "referenceId": "3"
    },
    "ref2": {
      "id": "3",
      "name": "Third"
    }
  }
]

This is pretty close to what I want, however, I am stuck at this stage with the following problems:

  • how can I remove the unwanted fields? Because of the $unwinding for the $lookup, I have duplicates of fields which I don't want in my final result (ref1 where ref2 was looked up and ref2 where ref1)
  • how do I group the result into one object by the dynamic (aka unpredictable) field names (in this case ref1 and ref2?)

Is this doable in MongoDB? Am I on the right path or is this something I should consider doing in client (as in backend) code? I would prefer to solve this in aggregation since client code will have a hit on performance.

Here are the steps I am taking up so far:

Mongo playground

  1. Convert the result into an array via $objectToArray, and fetch only the keys I am interested in by checking for existence of the referenceId key. If present, return the [key, value] pair, null otherwise: (match for the 2nd object from data above)
  {
    $match: {
      id: "2"
    }
  }
  {
    $addFields: {
      "references": {
        $map: {
          input: {
            "$objectToArray": "$$ROOT"
          },
          as: "item",
          in: {
            $cond: {
              if: {
                $ne: [
                  "$$item.v.referenceId",
                  undefined
                ]
              },
              then: [
                "$$item.k",
                "$$item.v.referenceId"
              ],
              else: null
            }
          }
        }
      }
    }
  }
  1. Filter out the null values for a subsequent map:
  {
    "$addFields": {
      "references": {
        $filter: {
          input: "$references",
          cond: {
            $ne: [
              "$$this",
              null
            ]
          }
        }
      }
    }
  }
  1. For a lookup I need fixed field names, therefore convert the results into an array of {"k": , "v": "}
{
    "$addFields": {
      "references": {
        $map: {
          input: "$references",
          as: "item",
          in: {
            k: {
              "$arrayElemAt": [
                "$$item",
                0
              ]
            },
            v: {
              "$arrayElemAt": [
                "$$item",
                1
              ]
            }
          }
        }
      }
    }
  }
  1. Unwind the results to have a v field to perform the lookup with:
  {
    $unwind: "$references"
  },
  {
    $unwind: "$references.v"
  }
  1. Perform the lookup into a "resolved" field, unwind it, and convert it to an object with the name of (~ ref1, ref2 in my example above) and add it to the result document.
{
    $lookup: {
      "from": "collection",
      "localField": "references.v",
      "foreignField": "id",
      "as": "resolved"
    }
  },
  {
    $unwind: "$resolved"
  },
  {
    "$addFields": {
      "k": "$references.k",
      "v": "$resolved"
    }
  },

and finally remove the temporary fields

  {
    $project: {
      resolved: 0,
      references: 0,
    }
  },

Anyone up for the challenge or am I just trying too hard? ;-)

0 Answers
Related