Mongodb Question posted in
The official documentation can be found here.

Mongodb – How do I perform a lookup on a field in an array nested in another array?

I have a mongo collection (tables) that has documents of the form:

{
  "tableName": "Items",
  "rows": [
    {
      "label": "row 1 label here",
      "items": ["item1", "item2", "item3"]
    },
    {
      "label": "row 2 label here",
      "items": ["item4", "item5", "item6"]
    }
  ]
}

And I have another collection (items) that has documents of the form:

{
  "id": "item1",
  "name": "Item name",
  "code": "XXX-XXXX-XXXX",
  "price": 100
}

Now I want to query all items in a given table, so I am using the following aggregation:

{
  "$lookup": {
    from: "items",
    localField: "rows.items",
    foreignField: "id",
    as: "rows.items"
  }
}

So I am expecting all of "item1", "item2", … to be replaced with their corresponding documents as such:

{
  "tableName": "Items",
  "rows": [
    {
      "label": "row 1 label here",
      "items": [
        {
          "id": "item1",
          "name": "Item name",
          "code": "XXX-XXXX-XXXX",
          "price": 100
        },
        ... // the rest of the items
      ]
    },
    ... // the rest of the rows
  ]
}

But instead, "rows" is returned as an object, containing only the first object in the expected array, and the label field is even gone:

{
  "tableName": "Items",
  "rows": { // <-- rows is returned as on object, should be array
    "items": [  // <-- the "label" field is missing
      {
        "id": "item1",
        "name": "Item name",
        "code": "XXX-XXXX-XXXX",
        "price": 100
      },
      ... // the rest of the items
    ]
  }
}

So how do I preserve the outer array and the missing fields?

Tags:

2

Answers


  1. 0

    This is the reason why I am personally against the usage of nested arrays. It introduces unnecessary complexity to the queries.

    You may need to $unwind twice at rows and rows.items level to flatten the arrays. Perform the $lookup and $mergeObjects with the lookup result. You will then need to do 2 $group to revert back to your original structure.

    db.tables.aggregate([
  {
    "$unwind": "$rows"
  },
  {
    "$unwind": "$rows.items"
  },
  {
    "$lookup": {
      "from": "items",
      "localField": "rows.items",
      "foreignField": "id",
      "as": "itemsLookup"
    }
  },
  {
    "$unwind": "$itemsLookup"
  },
  {
    "$set": {
      "rows": {
        "$mergeObjects": [
          "$rows",
          "$itemsLookup"
        ]
      }
    }
  },
  {
    "$group": {
      "_id": {
        "_id": "$_id",
        "label": "$rows.label"
      },
      "tableName": {
        "$first": "$tableName"
      },
      "rows": {
        "$push": "$rows"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id._id",
      "tableName": {
        "$first": "$tableName"
      },
      "rows": {
        "$push": {
          "label": "$_id.label",
          "items": "$rows"
        }
      }
    }
  }
])

    Mongo Playground

    If you find yourself working at rows.items level frequently, consider refactoring the schema to store the entries as individual documents like this. You can gain potential performance boost through indexing too.

    Login or Signup to reply.
  2. 0

    Another option is to avoid $unwind and $group back, and replace them with $reduce and $map:

    db.tables.aggregate([
  {$set: {items: {$reduce: {
          input: "$rows",
          initialValue: [],
          in: {$concatArrays: ["$$value", "$$this.items"]}
  }}}},
  {$lookup: {
      from: "items",
      localField: "rows.items",
      foreignField: "id",
      as: "itemsLookup"
  }},
  {$set: {
      rows: {$map: {
          input: "$rows",
          as: "row",
          in: {$mergeObjects: [
              "$$row",
              {items: {$map: {
                    input: "$$row.items",
                    as: "item",
                    in: {
                      $arrayElemAt: [
                        "$itemsLookup",
                        {$indexOfArray: ["$itemsLookup.id", "$$item"]}
                      ]
                  }
              }}}
          ]}
      }},
      itemsLookup: "$$REMOVE",
      items: "$$REMOVE"
  }}
])

    See how it works on the playground example

    • I agree with @ray on the recommendation to reconsider your schema according to your needs
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search