MongoDB Lookup values based on dynamic field name

Question

I'm pretty sure the below can be done, I'm struggling to understand how to do it in MongoDB.

My data is structured like this (demo data):

db={
  "recipes": [
    {
      "id": 1,
      "name": "flatbread pizza",
      "ingredients": {
        "1010": 1,
        "1020": 2,
        "1030": 200
      }
    },
    {
      "id": 2,
      "name": "cheese sandwich",
      "ingredients": {
        "1040": 1,
        "1050": 2
      }
    }
  ],
  "ingredients": [
    {
      "id": 1010,
      "name": "flatbread",
      "unit": "pieces"
    },
    {
      "id": 1020,
      "name": "garlic",
      "unit": "clove"
    },
    {
      "id": 1030,
      "name": "tomato sauce",
      "unit": "ml"
    },
    {
      "id": 1040,
      "name": "bread",
      "unit": "slices"
    },
    {
      "id": 1050,
      "name": "cheese",
      "unit": "slices"
    }
  ]
}

The output I'm trying to achieve would look like this:

  [
    {
      "id": 1,
      "name": "flatbread pizza",
      “flatbread”: “1 pieces”,
      “garlic”: “2 cloves”,
      “tomato sauce”: “200 ml”
    },
    {
      "id": 2,
      "name": "cheese sandwich",
      “bread”: “1 slices”,
      “cheese”: “2 slices”
    }
  ]

I've tried several approaches, and I get stuck at the bit where I need to do a lookup based on the ingredient name (which actually is the id). I tried using $objectToArray to turn it into a k-v document, but then I get stuck in how to construct the lookup pipeline.

Answer 1

This is not a simple solution, and probably can be improved:

db.recipes.aggregate([
  {
    "$addFields": {
      ingredientsParts: {
        "$objectToArray": "$ingredients"
      }
    }
  },
  {
    $unwind: "$ingredientsParts"
  },
  {
    "$group": {
      _id: "$id",
      name: {
        $first: "$name"
      },
      ingredientsParts: {
        $push: {
          v: "$ingredientsParts.v",
          id: {
            $toInt: "$ingredientsParts.k"
          }
        }
      }
    }
  },
  {
    "$lookup": {
      "from": "ingredients",
      "localField": "ingredientsParts.id",
      "foreignField": "id",
      "as": "ingredients"
    }
  },
  {
    $unwind: "$ingredients"
  },
  {
    "$addFields": {
      "ingredientsPart": {
        "$filter": {
          input: "$ingredientsParts",
          as: "item",
          cond: {
            $eq: [
              "$$item.id",
              "$ingredients.id"
            ]
          }
        }
      }
    }
  },
  {
    $project: {
      ingredients: 1,
      ingredientsPart: {
        "$arrayElemAt": [
          "$ingredientsPart",
          0
        ]
      },
      name: 1
    }
  },
  {
    "$addFields": {
      units: {
        k: "$ingredients.name",
        v: {
          "$concat": [
            {
              $toString: "$ingredientsPart.v"
            },
            " ",
            "$ingredients.unit"
          ]
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      name: {
        $first: "$name"
      },
      units: {
        $push: "$units"
      }
    }
  },
  {
    "$addFields": {
      "data": {
        "$arrayToObject": "$units"
      }
    }
  },
  {
    "$addFields": {
      "data.id": "$_id",
      "data.name": "$name"
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$data"
    }
  }
])

You can see it works here

As rickhg12hs said, it can be modeled better.