如何在$ project内部使用过滤器内部数组使用聚合

Question

目前，我正在使用mongoDB存储有关我的表格和响应的数据，当我在集合中使用查找时，返回以下数据：

[
    {
        "_id": 1,
        "forms": [
            {
                "current_version": 0,
                "history": [
                    {
                        "content": [{"label": "vrau"}],
                        "responses": [
                            {"client_id": 1, "response": [{"field": "vrau1"}]},
                            {"client_id": 1, "response": [{"field": "vrau1"}]},
                            {"client_id": 2, "response": [{"field": "vrau2"}]},
                            {"client_id": 2, "response": [{"field": "vrau2"}]},
                        ],
                    }
                ],
            }
        ],
        "name": "Testing",
    },
    {
        "_id": 2,
        "forms": [
            {
                "current_version": 1,
                "history": [
                    {
                        "content": [{"label": "vrau"}],
                        "responses": [
                            {"client_id": 1, "response": [{"field": "vrau11"}]},
                            {"client_id": 1, "response": [{"field": "vrau11"}]},
                            {"client_id": 2, "response": [{"field": "vrau22"}]},
                            {"client_id": 2, "response": [{"field": "vrau22"}]},
                        ],
                    }
                ],
            }
        ],
        "name": "Testing2",
    },
]

我要做的是仅返回第一个文档中的响应：_id _id = 1其中client_id等于1。因此，以下输出是我想要的。

[{
        "_id": 1,
        "forms": [
            {
                "history": [
                    {
                        "responses": [
                            {"client_id": 1, "response": [{"field": "vrau1"}]},
                            {"client_id": 1, "response": [{"field": "vrau1"}]},
                        ],
                    }
                ],
            }
        ]
    }]

但是我的查询是返回表单字段空的，这是我要做的：

collection.aggregate([
        {
           "$match" : {
               "_id" : 1,
           }
        },
        {
          "$project": {
             "forms": {
                "$filter": {
                   "input": "$forms",
                   "as": "form",
                   "cond": { "$and":[
                       {"$eq": [ "$$form.history.responses.client_id", 1 ]}
                       ]
                   }
                }
             }
          }
        }
    ])

上面的代码出了什么问题？

Answer 1

一种方法是使用$降低将您的多级数组“弄平”到一个响应中。然后，全部剩下的就是$ filter您需要的响应：

db.collection.aggregate([
  {$match: {_id: 1}},
  {
    $project: {
      responses: {
        $reduce: {
          input: "$forms",
          initialValue: [],
          in: {
            $concatArrays: [
              {
                $reduce: {
                  input: "$this.history",
                  initialValue: [],
                  in: {$concatArrays: ["$value", "$this.responses"]}
                }
              },
              "$value"
            ]
          }
        }
      }
    }
  },
  {
    $project: {
      responses: {
        $filter: {
          input: "$responses", as: "item", cond: {$eq: ["$item.client_id", 1]}
        }
      }
    }
  }
])

playground示例