In MongoDB I am searching in Nested Object Array but the result it is showing not appropriate-CodePudding

I am using collection Name "History" having below data

  [
      {
        "_id": {
          "$oid": "634a9d1b269c99e9364e8750"
        },
        "marks": [
          {
            "results": [
              {
                "product": "Abc",
                "score": 55
              }
            ]
          }
        ]
      },
      {
        "_id": {
          "$oid": "634a9fae269c99e9364e8755"
        },
        "marks": [
          {
            "results": [
              {
                "product": "Abc",
                "score": 10
              },
              {
                "product": "Xyz",
                "score": 5
              }
            ]
          }
        ]
      },
      {
        "_id": {
          "$oid": "634a9fae269c99e9364e8756"
        },
        "marks": [
          {
            "results": [
              {
                "product": "Abc",
                "score": 8
              },
              {
                "product": "Xyz",
                "score": 7
              }
            ]
          }
        ]
      },
      {
        "_id": {
          "$oid": "634a9fae269c99e9364e8757"
        },
        "marks": [
          {
            "results": [
              {
                "product": "Abc",
                "score": 7
              },
              {
                "product": "Xyz",
                "score": 8
              }
            ]
          }
        ]
      }
    ]

My Fetch command is this..

db.History.findOne({"marks.results.product":"Xyz"})

command executes without an error but it shows wrong results..

{
  "_id": {
    "$oid": "634a9fae269c99e9364e8755"
  },
  "marks": [
    {
      "results": [
        {
          "product": "Abc",
          "score": 10
        },
        {
          "product": "Xyz",
          "score": 5
        }
      ]
    }
  ]
}

The 1st object in results (product:"Abc") should not display as its not meet the criteria (Product="Xyz") please correct me and guaid me how to fetch desired data (objects only meet criteria i.e. Product="Xyz" )

CodePudding user response：

Your sample data here is a collection with 4 documents. 3 of these contain product="Xyz" along with other products. findOne finds one document that has product="Xyz" inside it, and return the document as is (without manipulating it). What you are requesting is to get back only a part of the document - meaning you want to manipulate the (double) nested array results in the returned answer. You can do it using an aggregation pipeline.

One option is to use $unwind for this

db.collection.aggregate([
  {$match: {"marks.results.product": "Xyz"}},
  {$limit: 1} // if you want only one document to return
  {$unwind: "$marks"},
  {$unwind: "$marks.results"},
  {$match: {"marks.results.product": "Xyz"}}
])

See how it works on the playground example - unwind

Another option is to $map and $filter:

See how it works on the playground example - filter