Filter result in mongo sub-sub array

Question

I have some collections such us this:

[{
  "_id": ObjectId("604f3ae3194f2135b0ade569"),
  "parameters": [
    {
      "_id": ObjectId("602b7455f4b4bf5b41662ec1"),
      "name": "Purpose",
      "options": [
        {
          "id": ObjectId("602b764ff4b4bf5b41662ec2"),
          "name": "debug",
          "sel": false
        },
        {
          "id": ObjectId("602b767df4b4bf5b41662ec3"),
          "name": "performance",
          "sel": false
        },
        {
          "id": ObjectId("602b764ff4b4bf5b41662ec4"),
          "name": "security",
          "sel": false
        },
        {
          "id": ObjectId("602b767df4b4bf5b41662ec5"),
          "name": "Not Applicable",
          "sel": false
        }
      ],
      "type": "multiple"
    },
    {
      "_id": ObjectId("602b79d35d4a1333b8b6e5ba"),
      "name": "Organization",
      "options": [
        {
          "id": ObjectId("602b79d353c89933b8238325"),
          "name": "SW",
          "sel": false
        },
        {
          "id": ObjectId("602b79d353c89933b8238326"),
          "name": "HW",
          "sel": false
        }
      ],
      "type": "multiple"
    }
  ]
}]

The parameters are most 30.

I need to implements in mongo a "filter" collections.

If I filter one or more parameters._id, mongo return:

1. collection _id that have match options.sel of this parameters._id
2. collection _id that have all options.sel equal to false of this parameters._id
3. non return collection _id if parameters._id has set up options.name:"Not Applicable" at value options.sel:true

For example, if I match parameters._id:ObjectId("602b7455f4b4bf5b41662ec1") and this parameters.options.id:ObjectId("602b764ff4b4bf5b41662ec2"), I expect:

• not collection _id that has, for parameters._id:ObjectId("602b7455f4b4bf5b41662ec1"), the specific parameters.options.id: ObjectId("602b767df4b4bf5b41662ec5") at value options.sel:true
• all collection _id that match with query
• all collection _id that has, for parameters._id:ObjectId("602b7455f4b4bf5b41662ec1"), all specific parameters.options.sel:false

Next I need to make this rule for more parameters.

I have think to implements three aggregation for every rule... Do you have suggestion?

Answer 1

Try this query:

db.testCollection.aggregate([
    { $unwind: "$parameters" },
    {
        $match: {
            "parameters._id": ObjectId("602b7455f4b4bf5b41662ec1"),
            "parameters.options.id": ObjectId("602b767df4b4bf5b41662ec5")
        }
    },
    {
        $addFields: {
            options: {
                $filter: {
                    input: "$parameters.options",
                    as: "option",
                    cond: {
                        $and: [
                            { $ne: ["$$option.sel", true] },
                            { $ne: ["$$option.name", "Not Applicable"] }
                        ]
                    }
                }
            }
        }
    }
])

Answer 2

With idea of @dheemanth-bath, I have make this query:

db.testCollection.aggregate([
    { $unwind: "$parameters" },
    {
        $match: {
            "parameters._id": ObjectId("602b7455f4b4bf5b41662ec1"),
        }
    },
    {
        $addFields: {
            match: {
                $filter: {
                    input: "$parameters.options",
                    as: "option",
                    cond: {
                        $and: [
                           { $eq: ["$$option.id", ObjectId('602b767df4b4bf5b41662ec5')] },
                           { $eq: ["$$option.sel", true] }
                        ]
                    }
                }
            },
            notDeclared: {
                $filter: {
                    input: "$parameters.options",
                    as: "option",
                    cond: {
                        $and: [
                           { $eq: ["$$option.name", "Not Applicable"]},
                           { $eq: ["$$option.sel", true] }
                        ]
                    }
                }
            }
        }
    }
])

The idea is that: after query count number of element of notDeclared. If > 0 waste the collection. Otherwise evaluate number of match, if is >0, there is at least one elements can match.

Good. But how I evaluate if all elements of options.sel are false?

And if I check another parameters, I need to make another aggregation (one for parameters)?