MongoDB中如何按条件查询内嵌对象数组

问题 #

我的MongoDB集合中每个文档都包含名为users的内嵌对象数组，需要按以下规则筛选该数组：

• 先筛选出status字段值为active的对象（仅部分对象包含status字段）；
• 获取上述符合条件对象的parent_user_id，匹配数组中其他对象的parent_user_id并筛选出这些对象；
• 用筛选后的结果替换原users数组返回，而非保留全部对象。

原集合文档示例 #

{    
    "_id" : ObjectId("63a8808652f40e1d48a3d1d7"),    
    "name" : "A",    
    "description" : null,    
    "users" : [
        {
            "id" : "63a8808c52f40e1d48a3d1da",
            "owner" : "John Doe",
            "purchase_date" : "2022-12-25",
            "status" : "active",
            "parent_user_id" : "63a8808c52f40e1d48a3d1da",
            "recent_items": ["tomato", "onion"]
        },
        {
            "id" : "63a880a552f40e1d48a3d1dc",
            "owner" : "John Doe 1",
            "purchase_date" : "2022-12-25",
            "parent_user_id" : "63a8808c52f40e1d48a3d1da",
            "recent_items": ["onion"]
        },
        {
            "id" : "63a880f752f40e1d48assddd",
            "owner" : "John Doe 2",
            "purchase_date" : "2022-12-25",
            "parent_user_id" : "63a8808c52f40e1d48a3d1da"
        },
        {
            "id" : "63a880f752f40e1d48a3d207",
            "owner" : "John Doe 11",
            "dt" : "2022-12-25",
            "status" : "inactive",
            "parent_user_id" : "63a880f752f40e1d48a3d207"
        },
        {
            "id" : "63a880f752f40e1d48agfmmb",
            "owner" : "John Doe 112",
            "dt" : "2022-12-25",
            "status" : "active",
            "parent_user_id" : "63a880f752f40e1d48agfmmb",
            "recent_items": ["tomato"]
        },
        {
            "id" : "63a880f752f40e1d48agggg",
            "owner" : "John SS",
            "dt" : "2022-12-25",
            "status" : "inactive",
            "parent_user_id" : "63a880f752f40e1d48agggg"
        },
        {
            "id" : "63a880f752f40e1d487777",
            "owner" : "John SS",
            "dt" : "2022-12-25",
            "parent_user_id" : "63a880f752f40e1d48agggg"
        }
    ]
}

期望返回结果 #

{  
    "_id" : ObjectId("63a8808652f40e1d48a3d1d7"),  
    "name" : "A",  
    "description" : null,  
    "users" : [
        {
            "id" : "63a8808c52f40e1d48a3d1da",
            "owner" : "John Doe",
            "purchase_date" : "2022-12-25",
            "status" : "active",
            "parent_user_id" : "63a8808c52f40e1d48a3d1da",
            "recent_items": ["tomato", "onion"]
        },
        {
            "id" : "63a880a552f40e1d48a3d1dc",
            "owner" : "John Doe 1",
            "purchase_date" : "2022-12-25",
            "parent_user_id" : "63a8808c52f40e1d48a3d1da"
        },
        {
            "id" : "63a880f752f40e1d48assddd",
            "owner" : "John Doe 2",
            "purchase_date" : "2022-12-25",
            "parent_user_id" : "63a8808c52f40e1d48a3d1da"
        },
        {
            "id" : "63a880f752f40e1d48agfmmb",
            "owner" : "John Doe 112",
            "dt" : "2022-12-25",
            "status" : "active",
            "parent_user_id" : "63a880f752f40e1d48agfmmb",
            "recent_items": ["tomato"]
        }
    ]
}

解决方案 #

使用MongoDB聚合框架可以实现需求，具体查询语句如下：

db.collection.aggregate([
  {
    $addFields: {
      // 提取所有status为active的用户的parent_user_id并去重
      activeParentIds: {
        $setUnion: [
          {
            $map: {
              input: { $filter: { input: "$users", cond: { $eq: ["$$this.status", "active"] } } },
              as: "activeUser",
              in: "$$activeUser.parent_user_id"
            }
          }
        ]
      }
    }
  },
  {
    $addFields: {
      // 筛选parent_user_id在activeParentIds中的用户，替换原users数组
      users: {
        $filter: {
          input: "$users",
          cond: { $in: ["$$this.parent_user_id", "$activeParentIds"] }
        }
      }
    }
  },
  {
    // 移除临时字段activeParentIds
    $project: { activeParentIds: 0 }
  }
])

步骤说明 #

1. 提取有效父ID：通过$filter筛选出users数组中status为active的对象，再用$map提取这些对象的parent_user_id，最后用$setUnion去重得到activeParentIds临时字段。
2. 筛选目标用户：再次使用$filter，保留users数组中parent_user_id属于activeParentIds的对象，直接替换原users数组。
3. 清理结果：通过$project移除临时字段activeParentIds，返回符合要求的文档结构。

内容的提问来源于stack exchange，提问作者BlackWooDZZ