CODEWITHSUNDEEP
mongodb
Ashley Waldron
Ashley Waldron

Reputation: 33

Mongo query to find documents with array elements that contain non-null values none of which match the specified value

I'm trying to find a mongo query which will find all documents that contain an array where none of the elements have an attribute with some value. However if all elements have null values or are missing for that attribute then that document should not be returned. Also documents which do not contain the array or where it is empty should be ignored.
i.e. only documents with some non-null value for that attribute which doesn't match the value should be returned.
e.g. In the following example I'm looking for all documents which do not have a myShoppingList element with a type value of TYPE2 so the following document would be returned

{
  "myShoppingList": [
    {
      "type": "TYPE1",
      "someOtherAttribute" : "testValue1"
    },
    {
      "type": null,
      "someOtherAttribute" : "testValue2"
    }
  ]
}

But the following documents would not be returned:

{
  "myShoppingList": [
    {
      "type": "TYPE1",
      "someOtherAttribute" : "testValue1"
    },
    {
      "type": "TYPE2", // value 'TYPE2' is present here so not interested
      "someOtherAttribute" : "testValue1"
    },
    {
      "type": null,
      "someOtherAttribute" : "testValue2"
    }
  ]
}

{
  "myShoppingList": [
    {
      "someOtherAttribute" : "testValue1" // no type value present
    },
    {
      "type": null,
      "someOtherAttribute" : "testValue1" // and type is null here
    }
  ]
}

{
  "myShoppingList": [] // empty list should be ignored
}

Upvotes: 0

Views: 3028

Answers (1)

Joe
Joe

Reputation: 28366

This would need to be a 2 part query that evaluates each individual array element (i.e. $elemMatch) for:

  • at least 1 field that is both not equal to TYPE2 and not null
  • no fields that are equal to TYPE2

In a find query that might look like:

db.collection.find({
  myShoppingList: {
    $elemMatch: {
      $and: [
        {"type": {$ne: "TYPE2"}},
        {"type": {$ne: null}}
      ]
    },
    $not: {$elemMatch: {type: "TYPE2"}}
  }
})

Playground

Upvotes: 2

Related Questions