MongoDB aggregate: Replacing an array of IDs with the corresponding objects from an array in the same document

Question

I have the following documents in a collection:

[{
    id: 1,
    name: 'My document 1',
    allItems: [{
        id: 'item1',
        name: 'My item 1'
    }, {
        id: 'item2',
        name: 'My item 2'
    }, {
        id: 'item3'
        name: 'My item 3'
    }],
    containers: [{
        id: 'container1',
        selectedItems: [
            'item3',
            'item1'
        ]
    }, {
        id: 'container2',
        selectedItems: [
            'item3'
        ]
    }]
},
...]

I now want to write a query to find the document with a specific container ID (i.e. container1) and return it, while transforming it's selectedItems array to hold the actual object values from the allItems array of the same document.

I want to get this document:

{
    id: 1,
    name: 'My document 1',
    container: {
        id: 'container1',
        selectedItems: [{
            id: 'item3'
            name: 'My item 3'
        }, {
            id: 'item1',
            name: 'My item 1'
        }
    }
}

Currently I have the following aggregation (Node.js):

db.collection.aggregate([{
    $match: {'containers.id': 'container1'},
    $addFields: {
        container: {
            $filter: {
                input: '$containers', 
                as: 'container', 
                cond: {
                    $eq: ['$$container.id', 'container1']
                }
            }
        }
    },
    $project: {containers: 0},
    $unwind: {path: '$container'}

    // I now have a new property "container" with the correct subdocument from the array
    // and removed the original array with all containers.
    // I now need a stage here to populate the container subdocuments in the `selectedItems` array

    $project: {allItems: 0} // remove the list of all items when not needed anymore in a following stage
}]);

I know there is $lookup, but as I want to populate the matching array items from the same document (not a differect collection), I think I don't need it. Even when specifying the same collection in a $lookup, it would populate the array with the root document of my collection and it would get very complex to unwind & match the needed properties of the subdocument.

I thought about making an own items collection, but I would need slower lookups there and my data does not need to have these relations.

I hope my question is clear, I'm thankful for any help I can get!

I'm doing some additional transforms in my real data like copying some properties from the original root document and hiding others, but that should not matter.

Thank you again!

turivishal · Accepted Answer · 2021-08-18 16:33:26Z

$match your conditions
$filter to iterate loop of containers array and filter by id
$arrayElemAt to select first element from above filtered result
$let to declare a variable and store do above process and store result
$filter to iterate loop of allItems array and filter items by above result

db.collection.aggregate([
  { $match: { "containers.id": "container1" } },
  {
    $project: {
      name: 1,
      container: {
        $let: {
          vars: {
            container: {
              $arrayElemAt: [
                {
                  $filter: {
                    input: "$containers",
                    cond: { $eq: ["$$this.id", "container1"] }
                  }
                },
                0
              ]
            }
          },
          in: {
            id: "$$container.id",
            selectedItems: {
              $filter: {
                input: "$allItems",
                cond: { $in: ["$$this.id", "$$container.selectedItems"] }
              }
            }
          }
        }
      }
    }
  }
])

Playground

The second version, you can do process stage by stage,

$match your conditions
$filter to iterate loop of containers array and filter by id
$arrayElemAt to select first element from above filtered result
$filter to iterate loop of allItems array and filter items by selectedItems
$$REMOVE will remove fields

db.collection.aggregate([
  { $match: { "containers.id": "container1" } },
  {
    $addFields: {
      containers: "$$REMOVE",
      container: {
        $arrayElemAt: [
          {
            $filter: {
              input: "$containers",
              cond: { $eq: ["$$this.id", "container1"] }
            }
          },
          0
        ]
      }
    }
  },
  {
    $addFields: {
      allItems: "$$REMOVE",
      "container.selectedItems": {
        $filter: {
          input: "$allItems",
          cond: { $in: ["$$this.id", "$container.selectedItems"] }
        }
      }
    }
  }
])

Playground

Tom Slabbaert · Accepted Answer · 2021-08-18 14:18:53Z

You are correct by not needing to use $lookup, a series of $map and $filter expressions is all you need.

The approach is simple, we start by filtering the required container, then we iteration over the selected container's selectedItems using $map and match the relevant fields for each item from the allItems field.

  {
    $match: {
      id: 1,
      
    }
  },
  {
    $project: {
      name: 1,
      container: {
        "$arrayElemAt": [
          {
            $map: {
              input: {
                $filter: {
                  input: "$containers",
                  as: "container",
                  cond: {
                    $eq: [
                      "$$container.id",
                      "container1"
                    ]
                  }
                },
                
              },
              as: "container",
              in: {
                "$mergeObjects": [
                  {
                    id: "$$container.id"
                  },
                  {
                    selectedItems: {
                      $map: {
                        input: "$$container.selectedItems",
                        as: "item",
                        in: {
                          "$arrayElemAt": [
                            {
                              $filter: {
                                input: "$allItems",
                                as: "filter",
                                cond: {
                                  $eq: [
                                    "$$filter.id",
                                    "$$item"
                                  ]
                                }
                              }
                            },
                            0
                          ]
                        }
                      }
                    }
                  }
                ]
              }
            }
          },
          0
        ]
      }
    }
  }
])

Mongo Playground

Thank you! The only problem for me with this solution is that my original documents can have different additional properties that I want to keep (they won't be in the transformed docs because of the exclusive $project). The second solution from @turivishal seems to work this way.

Collectives™ on Stack Overflow

MongoDB aggregate: Replacing an array of IDs with the corresponding objects from an array in the same document

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related