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

Question

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
}

Joe · Accepted Answer · 2021-03-12 23:53:43Z

2

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

answered Mar 12, 2021 at 23:53

Joe

29k4 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ashley Waldron Over a year ago

Works perfectly! It was the placement of the 2nd $not which was giving me trouble. Many thanks

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related