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

Question

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
    ]
  }
}

Edit: here are the Schemas for "row", "item" and "table" respectively:

const RowSchema = new Schema({
  label: String,
  items: [String]
}, {
  _id: false
})

const ItemSchema = new Schema({
  id: String,
  name: String,
  code: String,
  price: Number
}, {
  _id: false
})

const TableSchema = new Schema({
  tableName: String,
  rows: [RowSchema]
})

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

Answer 1

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