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?

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

You can use an aggregation pipeline

db.coll.aggregate([
   { 
      $match: {
        "categories.name": "CONSULTANT_AGREEMENT" //return all docs where there is at least one match
      }
   },
   {
     $unwind: "$categories"
   },
   {
     $match:{
       "categories.subCategories": {$size: 1}
     }
   }, //you can group again to bring back the original structure
])