How to query multi-level nested sub document objects when the sub document ID is known?

// 1
{
    "_id": ObjectId("62c238b03148c6cc6594dc9b"),
    "name": "首页",
    "icon_name": "HomeFilled",
    "router_name": "Home",
    "router_path": "/home",
    "role_group": [
        "admin",
        "common"
    ],
    "is_operate": false,
    "describe": "",
    "create_time": ISODate("2022-07-04T00:47:44.977Z"),
    "children": [ ],
    "__v": NumberInt("0")
}

// 2
{
    "_id": ObjectId("62c238b03148c6cc6594dc9c"),
    "name": "系统设置",
    "icon_name": "Setting",
    "router_name": "System",
    "router_path": "/system",
    "role_group": [
        "admin"
    ],
    "is_operate": false,
    "describe": "",
    "children": [
        {
            "name": "角色管理",
            "icon_name": "Postcard",
            "router_name": "Role",
            "router_path": "/system/role",
            "role_group": [
                "admin"
            ],
            "is_operate": false,
            "describe": "",
            "_id": ObjectId("62c238b03148c6cc6594dc9d"),
            "create_time": ISODate("2022-07-04T00:47:44.979Z"),
            "children": [ ]
        },
        {
            "name": "菜单管理",
            "icon_name": "Menu",
            "router_name": "Menu",
            "router_path": "/system/menu",
            "role_group": [
                "admin"
            ],
            "is_operate": false,
            "describe": "",
            "_id": ObjectId("62c238b03148c6cc6594dc9e"),
            "create_time": ISODate("2022-07-04T00:47:44.98Z"),
            "children": [ ]
        },
        {
            "name": "部门管理",
            "icon_name": "OfficeBuilding",
            "router_name": "Dept",
            "router_path": "/system/dept",
            "role_group": [
                "admin"
            ],
            "is_operate": false,
            "describe": "",
            "_id": ObjectId("62c238b03148c6cc6594dc9f"),
            "create_time": ISODate("2022-07-04T00:47:44.98Z"),
            "children": [ ]
        },
        {
            "name": "用户管理",
            "icon_name": "User",
            "router_name": "User",
            "router_path": "/system/user",
            "role_group": [
                "admin"
            ],
            "is_operate": false,
            "describe": "",
            "_id": ObjectId("62c238b03148c6cc6594dca0"),
            "create_time": ISODate("2022-07-04T00:47:44.98Z"),
            "children": [ ]
        }
    ],
    "create_time": ISODate("2022-07-04T00:47:44.98Z"),
    "__v": NumberInt("0")
}

For example, if the ID “62c238b03148c6cc6594dc9e” is known, how to query the object data where the ID is located?

The above is the data structure. Now it is two-level nesting. How to realize multi-level nested query?

Hi @sy1215zuigao and welcome in the MongoDB Community :muscle: !

I’m not sure what you are expecting in the output. If that’s not it, please provide the exact expected output you expect given the 2 docs in example. Also I’m not sure if the _id can appear in multiple docs or multiple times within the same doc. This would affect how you query as well depending what you want exactly.

Here is my proposition:

Insert docs:

db.coll.insertMany([{ "_id": ObjectId("62c238b03148c6cc6594dc9b"), "name": "首页", "icon_name": "HomeFilled", "router_name": "Home", "router_path": "/home", "role_group": [ "admin", "common"], "is_operate": false, "describe": "", "create_time": ISODate("2022-07-04T00:47:44.977Z"), "children": [], "__v": NumberInt("0") }, { "_id": ObjectId("62c238b03148c6cc6594dc9c"), "name": "系统设置", "icon_name": "Setting", "router_name": "System", "router_path": "/system", "role_group": [ "admin"], "is_operate": false, "describe": "", "children": [ { "name": "角色管理", "icon_name": "Postcard", "router_name": "Role", "router_path": "/system/role", "role_group": [ "admin"], "is_operate": false, "describe": "", "_id": ObjectId("62c238b03148c6cc6594dc9d"), "create_time": ISODate("2022-07-04T00:47:44.979Z"), "children": [] }, { "name": "菜单管理", "icon_name": "Menu", "router_name": "Menu", "router_path": "/system/menu", "role_group": [ "admin"], "is_operate": false, "describe": "", "_id": ObjectId("62c238b03148c6cc6594dc9e"), "create_time": ISODate("2022-07-04T00:47:44.98Z"), "children": [] }, { "name": "部门管理", "icon_name": "OfficeBuilding", "router_name": "Dept", "router_path": "/system/dept", "role_group": [ "admin"], "is_operate": false, "describe": "", "_id": ObjectId("62c238b03148c6cc6594dc9f"), "create_time": ISODate("2022-07-04T00:47:44.98Z"), "children": [] }, { "name": "用户管理", "icon_name": "User", "router_name": "User", "router_path": "/system/user", "role_group": [ "admin"], "is_operate": false, "describe": "", "_id": ObjectId("62c238b03148c6cc6594dca0"), "create_time": ISODate("2022-07-04T00:47:44.98Z"), "children": [] }], "create_time": ISODate("2022-07-04T00:47:44.98Z"), "__v": NumberInt("0") }])

Pipeline:

[
  {
    '$match': {
      'children._id': new ObjectId('62c238b03148c6cc6594dc9e')
    }
  }, {
    '$project': {
      'output': {
        '$filter': {
          'input': '$children', 
          'as': 'item', 
          'cond': {
            '$eq': [
              '$$item._id', new ObjectId('62c238b03148c6cc6594dc9e')
            ]
          }
        }
      }
    }
  }
]

Result:

> db.coll.aggregate([ { '$match': { 'children._id': new ObjectId('62c238b03148c6cc6594dc9e') } }, { '$project': { 'output': { '$filter': { 'input': '$children', 'as': 'item', 'cond': { '$eq': [ '$$item._id', new ObjectId('62c238b03148c6cc6594dc9e')] } } } } }])
[
  {
    _id: ObjectId("62c238b03148c6cc6594dc9c"),
    output: [
      {
        name: '菜单管理',
        icon_name: 'Menu',
        router_name: 'Menu',
        router_path: '/system/menu',
        role_group: [ 'admin' ],
        is_operate: false,
        describe: '',
        _id: ObjectId("62c238b03148c6cc6594dc9e"),
        create_time: ISODate("2022-07-04T00:47:44.980Z"),
        children: []
      }
    ]
  }
]

Cheers,
Maxime.

3 Likes

Thank you for your reply. I have changed the structure of data and no longer use nested data. Thank you again for your reply!

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.