Filter documents based on array size inside a root array element Mongodb

user_6304129 0
Viewed 43

enter image description here

Hello, I'd like to get all the documents where categories array has an element with key CONSULTANT_AGREEMENT and the respective subCategories array inside the CONSULTANT_AGREEMENT category contains only one element.

Is this possible in MongoDB or should I write a service function on top of the result set?

mongoose mongodb
2 Answers

This can also be done without unwinding, and by further limiting the amount of returned documents to perform aggregation on by filtering by both the categories.name and the size of the subCategories array.

db.collection.aggregate([
  {
    $match: {
      "categories": {
        $elemMatch: {
          "key": "CONSULTANT_AGREEMENT",
          "subCategories": {
            $size: 1
          }
        }
      }
    }
  },
  {
    $project: {
      "eg": {
        $filter: {
          input: "$categories",
          as: "category",
          cond: {
            $eq: [
              {
                $size: "$$category.subCategories"
              },
              1
            ]
          }
        }
      }
    }
  }
])

See here for a live example.

You can also add any additional fields that you want to the projection stage of the pipeline.

user_102186090

Yes!, I would personally elemMatch as this allows you to use the find cursor options and avoid the aggregation pipeline overhead.

like so:

db.collection.find({
  categories: {
    $elemMatch: {
      key: "CONSULTANT_AGREEMENT",
      $and: [
        {
          "subCategories.0": {
            $exists: true
          }
        },
        {
          "subCategories.1": {
            $exists: false
          }
        }
      ]
    }
  }
})

Mongo Playground

user_107703700
Related