How to use $filter to retrieve results from a nested array?

Question

I'm attempting to build a query using MongoDB, however I'm coming up against an issue where I am unable to filter an array within an array.

In my example below I am attempting to retrieve elements in the child data array within the parent list array, where the key systemDelete_DT is null.

I've attempted to use $eq: ["$$s_lists.customData.systemDelete_DT", null] as the filter condition, however no results load.

I am using $filter as this is part of larger aggregate with a $lookup.

How do I only retrieve results from the data array where the key systemDelete_DT is null?

Live Example

My Query

    db.collection.aggregate([
  {
    $lookup: {
      from: "s_list",
      localField: "_id",
      foreignField: "_id",
      as: "s_lists"
    }
  },
  {
    $project: {
      list: {
        $filter: {
          input: {
            $arrayElemAt: [
              "$s_lists.list",
              0
            ]
          },
          as: "s_lists",
          cond: {
           **// Note: This condition works as expected** 
            $eq: [
              "$$s_lists.systemDelete_DT",
              null
            ],
            
          }
        }
      }
    }
  }
])

Expected Result:

 [
      {
        "_id": ObjectId("6099619baa04f15c3fb67a83"),
        "list": [
          {
            "_id": ObjectId("609e70be503c58506ca77d3d"),
            "data": [
              {
                "_id": ObjectId("609e70be503c58506ca77d41"),
                "field_id": ObjectId("609c00d6de4c601e4fa358d8"),
                "systemDelete_DT": null
              },
              {
                "_id": ObjectId("609e936024c6a45adc030a4c"),
                "field_id": ObjectId("609e936024c6a45adc030a4b"),
                "systemDelete_DT": null
              }
            ],
            "systemDelete_DT": null
          }
        ]
      }
    ]

Database Example:

db={
  "collection": [
    {
      "_id": ObjectId("6099619baa04f15c3fb67a83")
    }
  ],
  "s_list": [
    {
      "_id": ObjectId("6099619baa04f15c3fb67a83"),
      "systemDelete_DT": null,
      "list": [
        {
          "_id": ObjectId("609e6ee1955f7b4fbd73fcb5"),
          "systemDelete_DT": "2021-05-14T18:13:18+01:00",
          "data": [
            {
              "_id": ObjectId("609e6ee1955f7b4fbd73fcb6"),
              "systemDelete_DT": "2021-05-14T18:13:18+01:00",
              "field_id": ObjectId("609bbeb0f1033c156f8e4d86"),
              
            },
            {
              "_id": ObjectId("609e6ee1955f7b4fbd73fcb9"),
              "systemDelete_DT": null,
              "field_id": ObjectId("609c00d6de4c601e4fa358d8"),
              
            },
            {
              "_id": ObjectId("609e936024c6a45adc030a4c"),
              "systemDelete_DT": null,
              "field_id": ObjectId("609e936024c6a45adc030a4b"),
              
            }
          ]
        },
        {
          "_id": ObjectId("609e70be503c58506ca77d3d"),
          "systemDelete_DT": null,
          "data": [
            {
              "_id": ObjectId("609e70be503c58506ca77d3e"),
              "systemDelete_DT": "2021-05-14T18:13:18+01:00",
              "field_id": ObjectId("609bbeb0f1033c156f8e4d86"),
              
            },
            {
              "_id": ObjectId("609e70be503c58506ca77d41"),
              "systemDelete_DT": null,
              "field_id": ObjectId("609c00d6de4c601e4fa358d8"),
              
            },
            {
              "_id": ObjectId("609e936024c6a45adc030a4c"),
              "systemDelete_DT": null,
              "field_id": ObjectId("609e936024c6a45adc030a4b"),
              
            }
          ]
        }
      ]
    }
  ]
}

turivishal · Accepted Answer · 2021-05-15 06:38:48Z

$filter to iterate loop of s_lists.list array and filter null elements after selecting first element using $arrayElemAt
$map to iterate loop of above filtered list result
$filter to iterate loop of inner data array and filter null elements
$mergeObjects to merge current fields with updated data field from above filter

  {
    $project: {
      list: {
        $map: {
          input: {
            $filter: {
              input: { $arrayElemAt: ["$s_lists.list", 0] },
              cond: { $eq: ["$$this.systemDelete_DT", null] }
            }
          },
          in: {
            $mergeObjects: [
              "$$this",
              {
                data: {
                  $filter: {
                    input: "$$this.data",
                    cond: { $eq: ["$$this.systemDelete_DT", null] }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }

Playground

Collectives™ on Stack Overflow

How to use $filter to retrieve results from a nested array?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related