how to query nested array of objects by also filtering nested array of nested array in mongo

Question

I have a mongodb (v.3.4) collection of Component objects which contains a list of SubComponents and SubComponent contains a list of Parts.

{
    "_id": "1_1",
    "name": "Component 1",
    "subComponents": [
        {
            "_id": "subId_1",
            "name": "subComponentName_subId_1",
            "type": "SUB_COMPONENT_TYPE_1",
            "parts": [
                {
                    "_id": "partId_1",
                    "type": "type_partId_1",
                    "description": "part description partId_1",
                    "status": true
                },
                {
                    "_id": "partId_2",
                    "type": "type_partId_2",
                    "description": "part description partId_2",
                    "status": true
                },
                {
                    "_id": "partId_3",
                    "type": "type_partId_3",
                    "description": "part description partId_3",
                    "status": true
                }
            ]
        },
        {
            "_id": "subId_2",
            "name": "subComponentName_subId_2",
            "type": "SUB_COMPONENT_TYPE_2",
            "parts": [
                {
                    "_id": "partId_1",
                    "type": "type_partId_1",
                    "description": "part description partId_1",
                    "status": true
                },
                {
                    "_id": "partId_2",
                    "type": "type_partId_2",
                    "description": "part description partId_2",
                    "status": true
                },
                {
                    "_id": "partId_3",
                    "type": "type_partId_3",
                    "description": "part description partId_3",
                    "status": true
                }
            ]
        },
        {
            "_id": "subId_3",
            "name": "subComponentName_subId_3",
            "type": "SUB_COMPONENT_TYPE_3",
            "parts": [
                {
                    "_id": "partId_1",
                    "type": "type_partId_1",
                    "description": "part description partId_1",
                    "status": true
                },
                {
                    "_id": "partId_2",
                    "type": "type_partId_2",
                    "description": "part description partId_2",
                    "status": true
                },
                {
                    "_id": "partId_3",
                    "type": "type_partId_3",
                    "description": "part description partId_3",
                    "status": true
                }
            ]
        }
    ],
    "_class": "com.ak.mongodb.domain.Component"
}

I want to query SubComponents of a Component (e.g. by componentId and subComponentId) and filter some parts of the SubCompoent by type at the same time.

So far I can filter by componentId and subComponentId but I get a Component Object!

Here is my aggregation:

db.components.aggregate([
    { $match: {
        _id: "1_1"
    }}, 
    { $project: {
        _id: 1,
        name: 1,
        subComponents: {
        $filter: { 
            input: "$subComponents", 
            as: "subComponent", 
            cond: { $eq: ["$$subComponent._id", "subId_1"]}
            }
        }
    }}
])

The result I'm getting:

{
    "_id" : "1_1",
    "name" : "Component 1",
    "subComponents" : [
        {
            "_id" : "subId_1",
            "name" : "subComponentName_subId_1",
            "type" : "SUB_COMPONENT_TYPE_1",
            "parts" : [
                {
                    "_id" : "partId_1",
                    "type" : "type_partId_1",
                    "description" : "part description partId_1",
                    "status" : true
                },
                {
                    "_id" : "partId_2",
                    "type" : "type_partId_2",
                    "description" : "part description partId_2",
                    "status" : true
                },
                {
                    "_id" : "partId_3",
                    "type" : "type_partId_3",
                    "description" : "part description partId_3",
                    "status" : true
                }
            ]
        }
    ]
}

What I'm trying to achive is a SubComponent with only parts of type "type_partId_1" and "type_partId_2":

{
            "_id" : "subId_1",
            "name" : "subComponentName_subId_1",
            "type" : "SUB_COMPONENT_TYPE_1",
            "parts" : [
                {
                    "_id" : "partId_1",
                    "type" : "type_partId_1",
                    "description" : "part description partId_1",
                    "status" : true
                },
                {
                    "_id" : "partId_2",
                    "type" : "type_partId_2",
                    "description" : "part description partId_2",
                    "status" : true
                }
            ]
        }

Ashh · Accepted Answer · 2019-07-24 16:37:13Z

To achieve this you need to add a few stages: first, $unwind the subComponent array, and define subComponenet as the new root of the document with $replaceRoot. Then, use a second $project stage to filter the parts array:

db.collection.aggregate([
  { "$match": { "_id": "1_1" }},
  { "$project": {
    "subComponents": {
      "$filter": {
        "input": "$subComponents",
        "as": "subComponent",
        "cond": { "$eq": ["$$subComponent._id", "subId_1"] }
      }
    }
  }},
  { "$unwind": "$subComponents" },
  { "$replaceRoot": { "newRoot": "$subComponents" }},
  { "$project": {
    "name": 1,
    "type": 1,
    "parts": {
      "$filter": {
        "input": "$parts",
        "as": "part",
        "cond": { "$in": ["$$part._id", ["partId_1", "partId_2"]] }
      }
    }
  }}
])

output:

[
  {
    "_id": "subId_1",
    "name": "subComponentName_subId_1",
    "type": "SUB_COMPONENT_TYPE_1",
    "parts": [
      {
        "_id": "partId_1",
        "description": "part description partId_1",
        "status": true,
        "type": "type_partId_1"
      },
      {
        "_id": "partId_2",
        "description": "part description partId_2",
        "status": true,
        "type": "type_partId_2"
      }
    ]
  }
]

you can try it online: mongoplayground.net/p/GhWTHnud-vm

Collectives™ on Stack Overflow

how to query nested array of objects by also filtering nested array of nested array in mongo

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related