How to query an array of objects where I need those documents which have all the objects with a matching filter?

Other MongoDB Topics Open Source Projects
,
1

Hello All,

I need some help in writing a query where in one of our collections we have a field which is an array(products) and it holds an array of objects . In each of this object we have a field called quantity1 and quanity2 where I need to fetch all the documents which has all the objects within that array has quantity1 and quantity2 as null.

Basically I need some thing exact opposite of what $nin can achieve.

Note: $in doesnt work because it will return even if there is one or more objects that doesn not match filter. The units within these quanitites can be anything as this is something coming from scraped data.

Example: For the below data the query should return only the 2nd document as its the only document which as all objects with quantity1 and quantity2 as null.

  {
    "_id": ObjectId("636a6aa584d5f92f14f0c548"),
    "products": [
      {
        "quantity1": '10 grams',
        "quantity2": '24 grams',
        "user_id": "602cf72a3fcad3cc605b8d59"
      },
      {
        "quantity1": '10 grams',
        "quantity2":  null,
        "user_id": "602cf72a3fcad3cc605b8d50"
      }
    ]
  },
  // 2
  {"_id": ObjectId("602e443bacdd4184511d6e29"),
  "products": [
    {
      "quantity1": 'null',
      "quantity2": 'null',
      "user_id": "602cf72a3fcad3cc605b8d59"
    },
    {
      "quantity1": 'null',
      "quantity2": 'null',
      "user_id": "602cf72a3fcad3cc605b8d59"
    },
    {
      "quantity1": 'null',
      "quantity2": 'null',
      "user_id": "602cf72a3fcad3cc605b8d59"
    }
  ]
},
// 3
{"_id": ObjectId("60332242acdd4184511ed664"),
"products": [
  {
    "quantity1": 'null',
    "quantity2": 'null',
    "user_id": "602cf72a3fcad3cc605b8d59"
  },
  {
    "quantity1": null,
    "quantity2": 'null',
    "user_id": "602cf72a3fcad3cc605b8d59"
  },
  {
    "user_id": "602cf72a3fcad3cc605b8d59"
  }
]
}
]```
2

Does this do what you wanted?

3

db.XYZ.find({$and:[{“products.0.quantity1”:{$all:[null]}},{“products.0.quantity2”:{$all:[null]}}]})

I tried this way but its not working for my case . As per the example I shared it should be giving only the 2nd doc however this is giving all 3 @John_Sewell

4 
db.collection.find({
  products: {
    $all: [
      {
        "$elemMatch": {
          quantity1: null
        }
      },
      {
        "$elemMatch": {
          quantity2: null
        }
      }
    ]
  }
})
5

This is still not working where if there is any one object which is satisfying its returning however I need only the ones which has all the objects with those fields as null @John_Sewell , Please check the attached monoglink to view the results.

6

You could probably switch it up and have a not over an or of them being populated. Im away from a computer today so cant try it at the moment.

7

Can any one please help with this use case ?, as I unable to write the exact query which works in this scenario.

@John_Sewell Any idea on how to achieve this ?

8

Tied up this morning but shall try and take a look this afternoon…

9

The tricky part is that a missing field evaluates to null. So we have to check the type. The type of a missing field is missing but its value is equal to null but the type of a field with a null value will be, euh, null.

So we have to check for {$type:null} rather than {$eq:null}.

Check the playground/p/cuJLrfnbK7B for a potential solution. I think that the last $set and $match can be combined into a single $match with $allElementsTrue.

I left out a final {$unset:_tmp} to cleanup temporary values.