MongoDB Self Join and select only subitems from Array of document

user_5627012 0
Viewed 181

I have data as below

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

I want to select only those members from a document which are not having is_removed flag 'true'

So above query should give me ["a2"] from the first document and ["a1"] from the second document.

In the end, i want to get all these member ids in one collection which we do via grouping but I am stuck on how to do document level filtering

Thanks in advance

aggregation-framework mongodb-query mongodb
1 Answers

Demo - 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 ] } 
            }
        }
     }
   }
])

Output

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