时间戳

首页 > 解决方案 > MongoDB Self Join 并从文档数组中仅选择子项

mongodb - MongoDB Self Join 并从文档数组中仅选择子项

问题描述

我有如下数据

[
  {
    "_id": 1,
    "members": [
      "a1",
      "a2"
    ],
    "member_profiles": [
      {
        "_id": "a1",
        "is_removed": true
      }
    ]
  },
  {
    "_id": 2,
    "members": [
      "a1",
      "a3"
    ],
    "member_profiles": [
      {
        "_id": "a3",
        "is_removed": true
      }
    ]
  }
]

我只想从文档中选择那些没有is_removed标志“真”的成员

所以上面的查询应该["a2"]从第一个文档和["a1"]第二个文档中给我。

最后，我想通过分组将所有这些成员 ID 放在一个集合中，但我被困在如何进行文档级过滤

提前致谢

标签： mongodbmongodb-queryaggregation-framework

解决方案

演示 - https://mongoplayground.net/p/WlWhxJtZn1D

db.collection.aggregate([
  { $unwind: "$member_profiles" }, // member_profiles into individual documents
  { $match: { "member_profiles.is_removed": true } }, // filter only is_removed is true
  { $group: { "_id": "$_id", members: { "$first": "$members" }, member_profiles: { "$push": "$member_profiles" } } }, // group records back
  {
    $project: { 
        "activeMembers": { 
            $filter: { // to remove null values
                input: {
                    "$map": { 
                        "input": "$members",
                        "as": "m",
                        "in": {
                            "$cond": {
                                "if": { "$in": [ "$$m",  "$member_profiles._id" ] }, // find if current member in member_profiles._id is there
                                "then": "$$m",
                                "else": null }
                                }
                        }
                    },
                as: "mem",
                cond: { $ne: [ "$$mem", null ] } 
            }
        }
     }
   }
])

输出

[
  {
    "_id": 2,
    "activeMembers": [
      "a1"
    ]
  },
  {
    "_id": 1,
    "activeMembers": [
      "a2"
    ]
  }
]

推荐阅读