Is there a Mongo function for filtering all nested/subdocuments based on a field?

user_1300533 0
Viewed 217

I have some documents in MongoDB that have other nested documents all with an "active" field. For instance :

{
    "id": "PRODUCT1",
    "name": "Product 1",
    "active": true,
    "categories": [
        {
            "id": "CAT-1",
            "active": true,
            "subcategories": [
                {
                    "id": "SUBCAT-1",
                    "active": false
                },
                {
                    "id": "SUBCAT-2",
                    "active": true
                }
            ]
        },
        {
            "id": "CAT-2",
            "active": false,
            "subcategories": [
                {
                    "id": "SUBCAT-3",
                    "active": true
                }
            ]
        }
    ]
}

Is there a way to find all documents but only keep the "active" nested documents.

This is the result I'd like :

{
    "id": "PRODUCT1",
    "name": "Product 1",
    "active": true,
    "categories": [
        {
            "id": "CAT-1",
            "active": true,
            "subcategories": [
                {
                    "id": "SUBCAT-2",
                    "active": true
                }
            ]
        }
    ]
}

Knowing that I do NOT know the document schema beforehand. That's why I need a sort of conditioned wildcard projection... (ie *.active=true). Is this possible or this HAS to be done serverside ?

spring-data-mongodb mongodb-query mongodb
3 Answers
user_26838140 
//actual code out from mongo shell 4.2 on windows
//sample document as shared in problem statement, query to find the document from //collection
> db.products.find().pretty();
{
        "_id" : ObjectId("5f748ee5377e73757bb7ceac"),
        "id" : "PRODUCT1",
        "name" : "Product 1",
        "active" : true,
        "categories" : [
                {
                        "id" : "CAT-1",
                        "active" : true,
                        "subcategories" : [
                                {
                                        "id" : "SUBCAT-1",
                                        "active" : false
                                },
                                {
                                        "id" : "SUBCAT-2",
                                        "active" : true
                                }
                        ]
                },
                {
                        "id" : "CAT-2",
                        "active" : false,
                        "subcategories" : [
                                {
                                        "id" : "SUBCAT-3",
                                        "active" : true
                                }
                        ]
                }
        ]
}
//verify mongo shell version no. for reference
> db.version();
4.2.6
//using aggregate and $unwind you can query the inner array elements as shown below
> db.products.aggregate([
... {$unwind: "$categories"},
... {$unwind: "$categories.subcategories"},
... {$match:{"active":true,
...          "categories.active":true,
...          "categories.subcategories.active":true}}
... ]).pretty();
{
        "_id" : ObjectId("5f748ee5377e73757bb7ceac"),
        "id" : "PRODUCT1",
        "name" : "Product 1",
        "active" : true,
        "categories" : {
                "id" : "CAT-1",
                "active" : true,
                "subcategories" : {
                        "id" : "SUBCAT-2",
                        "active" : true
                }
        }
}
>
user_141485600

You'll be able to achieve this with a few $map, $reduce and $filter stages.

db.collection.aggregate([
  {
    "$addFields": {
      "categories": {
        "$filter": {
          "input": "$categories",
          "cond": {
            $eq: [
              "$$this.active",
              true
            ]
          }
        }
      }
    }
  },
  {
    "$addFields": {
      "categories": {
        "$map": {
          "input": "$categories",
          "in": {
            "$mergeObjects": [
              "$$this",
              {
                "subcategories": {
                  "$filter": {
                    "input": "$$this.subcategories",
                    "cond": {
                      $eq: [
                        "$$this.active",
                        true
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Executing the above will give you the following result based on your input

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "active": true,
    "categories": [
      {
        "active": true,
        "id": "CAT-1",
        "subcategories": [
          {
            "active": true,
            "id": "SUBCAT-2"
          }
        ]
      }
    ],
    "id": "PRODUCT1",
    "name": "Product 1"
  }
]

https://mongoplayground.net/p/fkkby-eibx2

user_40799670
Related