mongodbaggregate

How To Query Unknown Key's Value Documents With Nested Structure in MongoDB?


I have a problem with querying with MongoDB, here's the data structure in MongoDB:

{
  "_id": "aaaaa",
  "groups": [
    {
      "name": "group0",
      "props": {
          "unknown1": "value1",
          "unknown2": "value2",
          "unknown3": "value3"
      }
    },
    {
      "name": "group1",
      "props": {
          "unknown4": "value4",
          "unknown5": "value5",
          "unknown6": "value6"
      }
    }
  ]
}

I want to query documents whose nested fields have specific value but the key is unknown , like : groups[X].props.unknownX.valueX = valueA.

I'v tried using aggregate query below :

db.collection.aggregate([
  {
    $project: {
      "props": "$groups.props"
    }
  },
  {
    $unwind: "$props"
  },
  {
    $project: {
      "result": {
        $objectToArray: "$props"
      }
    }
  },
  {
    $match: {
      $and: [
        {
          "result.k": "unknown2",
          "result.v": "value1"
        }
      ]
    }
  },
  {
    "$project": {
      result: {
        "$arrayToObject": "$result"
      }
    }
  }
])

A sandBox: https://mongoplayground.net/p/71TYThQnxTP

The result is :

[
  {
    "_id": "aaaaa",
    "result": {
      "unknown1": "value1",
      "unknown2": "value2",
      "unknown3": "value3"
    }
  }
]

But I expect it as :

[]

It should not be a array because I used $and operation by querying condition props.unknown2 = value1. But it seems return props.unknown2 exists and props.unknownX.value1 exists, is there anything wrong I make ?

Can anyone can help ? I will be grateful. Thank you!


Solution

  • You haven't checked that both criteria are satisfied by a single element of the array. This is exactly what $elemMatch is for:

    {
        $match: {
          result: {
            $elemMatch: {
              "k": "unknown2",
              "v": "value1"
            }
          }
        }
      }