Filter subdocument array but return all parent fields

[Seb_Toombs]

Hey,

I have a collection where each document might have a subdocument array.

I’m already using an aggregation pipeline to return the documents, but I want to filter the subdoc array before returning it.

I’m currently doing this in code after the database query, but was trying to investigate doing it in the database instead.

I’ve got something that works, however it only returns the subdoc array, and none of the original parent document items. Ideally I want all the original fields, and a filtered result set of the subdoc array.

Here’s a vague schema of my data:

{
  _id: ObjectID
  ... other fields that I still want returned
  feedback: [{ <- this array may be null or empty
    _id: ObjectID
    _recordStatus : String <- I want to filter by the value of this field which also may or may not exist
    ... other fields I want returned
  }]
}

And here’s the aggregation steps I’ve tried so far:

{
              $unwind: "$feedback",
            },
            {
              $match: {
                "feedback._recordStatus": {
                  $ne: "deleted",
                },
              },
            },
            {
              $group: {
                _id: {
                  feedback_id: "$feedback._id",
                },
                feedback: {
                  $push: "$feedback",
                },
              },
            }

This works, but discards all the original parent fields (except _id).

Any help would be amazing!

[Takis]

Hello

MQL has $filter to do that array operation

if array filter
else if exist old_value
else $$REMOVE

(is system variable,i used it , if feeback not exists,to not add the field keep document
as it was)

If the recordStatus dont exists document passes,because it cant be = “deleted”
you can change it,and if not exists remove the member,here it keeps it.

Missing : , but more concise print

{"aggregate" "testcoll",
 "pipeline"
 [{"$addFields"
   {"feedback"
    {"$switch"
     {"branches"
      [{"case" {"$isArray" ["$feedback"]},
        "then"
        {"$filter"
         {"input" "$feedback",
          "as" "record",
          "cond" {"$ne" ["$$record._recordStatus" "deleted"]}}}}
       {"case" {"$ne" [{"$type" "$feedback"} "missing"]},
        "then" "$feedback"}],
      "default" "$$REMOVE"}}}}],
 "cursor" {},
 "maxTimeMS" 1200000}

Same query valid JSON

{
  "aggregate": "testcoll",
  "pipeline": [
    {
      "$addFields": {
        "feedback": {
          "$switch": {
            "branches": [
              {
                "case": {
                  "$isArray": [
                    "$feedback"
                  ]
                },
                "then": {
                  "$filter": {
                    "input": "$feedback",
                    "as": "record",
                    "cond": {
                      "$ne": [
                        "$$record._recordStatus",
                        "deleted"
                      ]
                    }
                  }
                }
              },
              {
                "case": {
                  "$ne": [
                    {
                      "$type": "$feedback"
                    },
                    "missing"
                  ]
                },
                "then": "$feedback"
              }
            ],
            "default": "$$REMOVE"
          }
        }
      }
    }
  ],
  "cursor": {},
  "maxTimeMS": 1200000
}

[Seb_Toombs]

Amazing! Thanks so much. I’d been wondering whether addFields was what I needed.

I didn’t know how to use switch here either.

Works perfectly I think!