Return document where all elements of nested array match

Question

I'm trying to write a query that return all documents where descrtratt: "Chemioterapia" and nomemed: "null".

This is what I tried:

db.pazienti.find({terapie: {$elemMatch: {descrtratt: "Chemioterapia", nomemed: "null"}}})

The query I wrote, however, returns documents where at least one element of the terapie array match, what I want is return all documents where ALL the elements of the terapie array match.

Considering these 4 inputs documents below, the returned documents should be only the 2nd and the 4th one. My query returns all 4 documents instead, how should I fix it?

{
"codf": "001", 
"nome": "Michelangelo", 
"cognome": "Milani", 
"terapie": [
    {
        "descrtratt": "Chemioterapia", 
        "nomemed": "null", 
    }, 
    {
        "descrtratt": "Chemioterapia",  
        "nomemed": "Busulfano",  
    }
    ]
}
{
"codf": "004", 
"nome": "Gigio", 
"cognome": "Battisti", 
"terapie": [
    {
        "descrtratt": "Chemioterapia", 
        "nomemed": "null", 
    }, 
    {
        "descrtratt": "Chemioterapia",  
        "nomemed": "null",  
    }
    ]
}
{
"codf": "007", 
"nome": "Giovanni", 
"cognome": "Bomba", 
"terapie": [
    {
        "descrtratt": "Chemioterapia", 
        "nomemed": "null", 
    }, 
    {
        "descrtratt": "Radioterapia",  
        "nomemed": "null",  
    }
    ]
}
{
"codf": "023", 
"nome": "Luca", 
"cognome": "Agostinelli", 
"terapie": [
    {
        "descrtratt": "Chemioterapia",  
        "nomemed": "null",  
    }
    ]
}

turivishal · Accepted Answer · 2021-07-02 19:50:53Z

1

You can use negative condition $ne inside $elemMatch with $nor operator,

db.pazienti.find({
  $nor: [
    {
      terapie: {
        $elemMatch: {
          descrtratt: { $ne: "Chemioterapia" }
        }
      }
    },
    {
      terapie: {
        $elemMatch: {
          nomemed: { $ne: "null" }
        }
      }
    }
  ]
})

Playground

edited Jul 2, 2021 at 19:50

answered Jul 2, 2021 at 16:17

turivishal

36.4k7 gold badges47 silver badges69 bronze badges

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

2 Comments

Piero.Sgri Over a year ago

This is not returning the last document however, the one with only one element in the array, do you know why? Edit: I double checked also in the playground you linked, this is returning also a wrong document, the 2nd one, where "desctratt" is "Radioterapia" not "Chemioterapia"

turivishal Over a year ago

you are right, i have updated the answer.

Collectives™ on Stack Overflow

Return document where all elements of nested array match

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related