CODEWITHSUNDEEP
mongodbmongodb-query
Suganth
Suganth

Reputation: 35

How to query an array of nested data in MongoDB

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:

  1. 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})
  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")
                }
            ]

Upvotes: 1

Views: 54

Answers (1)

Charchit Kapoor
Charchit Kapoor

Reputation: 9284

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.

Upvotes: 1

Related Questions