Querying an array of key value pairs using $and with nested $and logical operator

user_3843332 0
Viewed 169

Given the following MongoDB collection:

[
  {
    "_id": 1,
    "basket": [
      {
        "key": "A",
        "value": [
          "Bananas"
        ]
      },
      {
        "key": "B",
        "value": [
          "Apples"
        ]
      }
    ]
  },
 {
    "_id": 2,
    "basket": [
      {
        "key": "A",
        "value": [
          "Oranges"
        ]
      },
      {
        "key": "B",
        "value": [
          "Bananas"
        ]
      }
    ]
  },
  {
    "_id": 3,
    "basket": [
      {
        "key": "A",
        "value": [
          "Bananas"
        ]
      },
      {
        "key": "B",
        "value": [
          "Bananas"
        ]
      }
    ]
  },
 {
    "_id": 4,
    "basket": [
      {
        "key": "A",
        "value": [
          "Oranges"
        ]
      },
      {
        "key": "B",
        "value": [
          "Apples"
        ]
      }
    ]
  }
]

I want to query this collection to get all documents where "Bananas" appears on basket 'A' and 'B', meaning that the expected result would be:

[
 {
    "_id": 3,
    "basket": [
      {
        "key": "A",
        "value": [
          "Bananas"
        ]
      },
      {
        "key": "B",
        "value": [
          "Bananas"
        ]
      }
    ]
  }
]

This is the actual outcome:

[
  {
    "_id": 1,
    "basket": [
      {
        "key": "A",
        "value": [
          "Bananas"
        ]
      },
      {
        "key": "B",
        "value": [
          "Apples"
        ]
      }
    ]
  },
 {
    "_id": 2,
    "basket": [
      {
        "key": "A",
        "value": [
          "Oranges"
        ]
      },
      {
        "key": "B",
        "value": [
          "Bananas"
        ]
      }
    ]
  },
  {
    "_id": 3,
    "basket": [
      {
        "key": "A",
        "value": [
          "Bananas"
        ]
      },
      {
        "key": "B",
        "value": [
          "Bananas"
        ]
      }
    ]
  }
]

So, for some reason that I don't quite get why, I'm getting all the documents where "Bananas" appear in any basket when I should be getting only the document with the _id: 3

This is the query I'm using:

{
  $and: [
   {
      $and: [ 
       { "basket.value": { $in: ["Bananas"] } }, 
       { "basket.key": { $eq: "A" } }
      ]
   },
   {
    $and: [ 
     { "basket.value": { $in: ["Bananas"] } }, 
     { "basket.key": { $eq: "B" } }
    ]
   } 
  ]
}
aggregation-framework mongodb-query mongodb
1 Answers

Try running a query that uses the $expr operator as it allows you to leverage aggregation pipeline operators which are best suited for the above query. Essentially you need to filter the basket array on the above condition i.e. where the value "Bananas" is in the values array AND where the key value is either 'A' OR 'B'. This condition as expressed in the aggregation pipeline is as follows:

{
    $and: [ 
      { $in: ['Bananas', '$$basket_element.value'] }, 
      { $in: ['$$basket_element.key', ['A', 'B'] ] }
    ]
}

The length of this filtered array should be equal to 2 for the overall query to be satisfied thus you would need the operators

  • $filter - returns the basket array filtered on the above condition
  • $size - returns the length of the filtered array
  • $eq - is the conditional operator used by $expr query to compare the size of the filtered array, which should have exactly 2 elements.

Overall, your query should be as follows:

db.collection.find({
   $expr: {
       $eq: [
          { $size: {
             $filter: {
                 input: '$basket',
                 as: 'basket_element',
                 cond: {
                     $and: [ 
                       { $in: ['Bananas', '$$basket_element.value'] }, 
                       { $in: ['$$basket_element.key', ['A', 'B'] ] }
                     ]
                 }
              }      
          } },
          2
       ]
   }
})

Mongo Playground

user_1220050
Related