How to retrieve a single object in an array from a document

Question

I'm struggling to retrieve a single object from an array within a document, where the key systemDelete_DT is null - everything i've tried either retrieve the whole array or nothing.

In my example below I would like to only retrieve the object where the _id matches the map.groupId and map.systemDelete_DT is null.

I've tried using $elemMatch and $filter among others.... to no avail.

How do I retrieve only the object where the _id matches the map.groupId and map.systemDelete_DT is null?

My Query

db.collection.aggregate([
  {
    $lookup: {
      from: "products",
      let: {
        id: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$$id",
                "$map.groupId"
              ]
            },
            
          },
          
        },
        {
          $project: {
            _id: 1,
            "map.groupId": 1,
            
          },
          
        },
        
      ],
      as: "p_map",
      
    },
    
  },
  {
    $project: {
      p_map: 1
    }
  }
])

Expect Result

    {
      "_id": ObjectId("609bc0c7c2a4e3164662238a"),
      "systemDelete_DT": null,
      "groupId": ObjectId("6099619baa04f15c3fb67a83")
    }

Example Collection

db={
  "collection": [
    {
      "_id": ObjectId("6099619baa04f15c3fb67a83")
    }
  ],
  "products": [
    {
      "_id": ObjectId("608d92fdc90b0ea83a8b52bb"),
      "map": [
        {
          "_id": ObjectId("609bc0c7c2a4e3164662238a"),
          "systemDelete_DT": null,
          "groupId": ObjectId("6099619baa04f15c3fb67a83")
        },
        {
          "_id": ObjectId("609bc0c7c2a4e3164662238a"),
          "systemDelete_DT": "2021-05-14T15:01:20+01:00",
          "groupId": ObjectId("6099619baa04f15c3fb67a83")
        },
        {
          "_id": ObjectId("609bd0bbaffb7018a41c2f1e"),
          "systemDelete_DT": "2021-05-12T13:57:34+01:00",
          "groupId": ObjectId("609964e44cc4275e520d3df0")
        },
        {
          "_id": ObjectId("609c019bad4cd31e7bc569bf"),
          "systemDelete_DT": "2021-05-12T17:26:43+01:00",
          "shopifyId": "6670290944179",
          "groupId": ObjectId("6099a006a8480e782ddc1d87")
        },
        
      ],
      
    }
  ]
}

Live Demo

Answer 1

• $match systemDelete_DT null condition
• $filter to iterate loop of map and filter by both condition
• $arrayElemAt to select first object from map
• $replaceRoot to replace object from lookup after select object using $arrayElemAt

db.collection.aggregate([
  {
    $lookup: {
      from: "products",
      let: { id: "$_id" },
      pipeline: [
        {
          $match: {
            "map.systemDelete_DT": null,
            $expr: { $in: ["$$id", "$map.groupId"] }
          }
        },
        {
          $project: {
            _id: 1,
            map: {
              $arrayElemAt: [
                {
                  $filter: {
                    input: "$map",
                    cond: {
                      $and: [
                        { $eq: ["$$this.groupId", "$$id"] },
                        { $eq: ["$$this.systemDelete_DT", null] }
                      ]
                    }
                  }
                },
                0
              ]
            }
          }
        }
      ],
      as: "p_map"
    }
  },
  {
    $replaceRoot: {
      newRoot: { $arrayElemAt: ["$p_map.map", 0] }
    }
  }
])

Playground

Answer 2

You can use

• $unwind to deconstruct the array
• $group to reconstruct the array
• $and is to make sure both condition should be true

Here is the code

db.collection.aggregate([
  {
    $lookup: {
      from: "products",
      let: { id: "$_id" },
      pipeline: [
        { $unwind: "$map" },
        {
          $match: {
            $expr: {
              $and: [
                { $eq: [ "$$id", "$map.groupId" ] },
                { $eq: [ "$map.systemDelete_DT", null ] }
              ]
            }
          }
        },
        {
          $group: {
            _id: "$_id",
            map: { $push: "$map" }
          }
        }
      ],
      as: "p_map",
      
    }
    
  },
  {
    $project: {
      p_map: 1
    }
  }
])

Working Mongo playground