How to query an array of nested data in MongoDB-CodePudding

This is my collection in my db:

{
    "items": [
        {
            "id": "1",
            "audit": [
                {
                    "validFrom": ISODate("2021-01-20T14:24:57.483Z"),
                    "validTo": ISODate("2024-01-20T14:24:57.483Z")
                }
            ]
        },
        {
            "id": "1",
            "audit": [
                {
                   "validFrom": ISODate("2021-01-19T14:24:57.483Z"),
                   "validTo": ISODate("2024-01-19T14:24:57.483Z")
                }
            ]
        }
    ]
}

Part 1:

I wanted to query validFrom. And while querying, I want to display that specific audit element alone. I tried these queries:

This query returned only the first element that matched the condition

db.Balances.find({"items.audit.validto":{"$lte": ISODate("2024-01-20T14:24:57.483Z")}},{"items.$":1})

This query returned all data of that collection alone irrespective of the filter

db.Balances.find({""items.audit.validto":{"$lte": ISODate("2024-01-20T14:24:57.483Z")}},{"items":1})

Part 2:

After getting the desired result, I want to display the audit list alone instead of the entire item list

Expected Output:

            "audit": [
                {
                    "validFrom": ISODate("2021-01-20T14:24:57.483Z"),
                    "validTo": ISODate("2024-01-20T14:24:57.483Z")
                }
            ]

CodePudding user response：

This is one way of doing it using an aggregation pipeline.

Unwind the items array. Filter out the elements matching the criteria. Filter out the elements in the audit array matching the criteria.

db.collection.aggregate([
  {
    "$unwind": "$items"
  },
  {
    "$match": {
      "items.audit.validTo": {
        "$lte": ISODate("2024-01-20T14:24:57.483Z")
      }
    }
  },
  {
    "$project": {
      "audit": {
        "$filter": {
          "input": "$items.audit",
          "as": "elem",
          "cond": {
            "$lte": [
              "$$elem.validTo",
              ISODate("2024-01-20T14:24:57.483Z")
            ]
          }
        }
      },
      _id: 0
    }
  }
])

Playground link.