how to use aggregate with filter inner array inside $project

user_9670713 0
Viewed 22

Currently I'm using mongodb to store data about my forms and responses, when I using a find in my collection the following data is returned:

[
    {
        "_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",
    },
]

What I'm trying to do is return only the responses from the first document: _id = 1 where the client_id is equal to 1. so, the following output is what I want.

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

but my query Is returning the forms field empty, this is what I'm trying to do:

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

What is wrong with the code above?

pymongo mongodb
1 Answers

One way to do it is to use $reduce to "flatten" your multi level arrays into one array of responses. Then, all is left is to $filter the responses you need:

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 example

user_184823100
Related