Is possible use the operators "$ne" and "$elemMatch" in same query?

Question

I'm trying to find all documents that tipo=CD and canciones does not contains subdocuments that has title=Pajaritos and cancion=2

This is what i like to achieve in canciones field:

not (title=Pajaritos and cancion=2)

I have the following collection:

{ 
    "tipo": "CD",
    "Artista":"Los piratas",
    "Titulo": "Recuerdos",
    "canciones": [
        {
            "cancion":1,
            "titulo":"Adios mi barco",
            "longitud": "3:20"
         },
         {
            "cancion":2,
            "titulo":"Pajaritos",
            "longitud": "4:15"
         }
     ]
},
{ 
    "tipo": "CD",
    "Artista":"Los piratas",
    "Titulo": "Recuerdos",
    "canciones": [
        {
            "cancion":1,
            "titulo":"Adios mi barco",
            "longitud": "3:20"
        },
        {
            "cancion":3,
            "titulo":"Pajaritos",
            "longitud": "4:15" 
        }
    ]
},

{ 
    "tipo": "CD",
    "Artista":"Los piratas",
    "Titulo": "Recuerdos",
    "canciones": [
        {
            "cancion":1,
            "titulo":"Adios mi barco",
            "longitud": "3:20"
        },
        {
            "cancion":3,
            "titulo":"Pajaritos",
            "longitud": "4:15" 
        },
        {
            "cancion":4,
            "titulo":"Pajaritos4",
            "longitud": "44:15" 
        }
 }

My query

db.media.find({"tipo" : "CD" ,"canciones" : { "$ne" : {"$elemMatch" :  {"titulo" : "Pajaritos", "cancion" : 2}}}})

My undesired results

{ "_id" : ObjectId("550f0a1c009ed3a1c9bbcdc1"), "tipo" : "CD", "Artista" : "Los piratas", "Titulo" : "Recuerdos", "canciones" : [ { "cancion" : 1, "titulo" : "Adios mi barco", "longitud" : "3:20" }, { "cancion" : 2, "titulo" : "Pajaritos", "longitud" : "4:15" } ] }
{ "_id" : ObjectId("550f0a1c009ed3a1c9bbcdc2"), "tipo" : "CD", "Artista" : "Los piratas", "Titulo" : "Recuerdos", "canciones" : [ { "cancion" : 1, "titulo" : "Adios mi barco", "longitud" : "3:20" }, { "cancion" : 3, "titulo" : "Pajaritos", "longitud" : "4:15" } ] }
{ "_id" : ObjectId("550f0a1c009ed3a1c9bbcdc3"), "tipo" : "CD", "Artista" : "Los piratas", "Titulo" : "Recuerdos", "canciones" : [ { "cancion" : 1, "titulo" : "Adios mi barco", "longitud" : "3:20" }, { "cancion" : 3, "titulo" : "Pajaritos", "longitud" : "4:15" }, { "cancion" : 4, "titulo" : "Pajaritos4", "longitud" : "44:15" } ] }

As you can see, the results contains "titulo" : "Pajaritos" which is trying to avoid.

Trying with $not operator instead of $ne

{ "_id" : ObjectId("550f173fc357b7a4e7a46e10"), "tipo" : "CD", "Artista" : "Los piratas", "Titulo" : "Recuerdos", "canciones" : [ { "cancion" : 1, "titulo" : "Adios mi barco", "longitud" : "3:20" }, { "cancion" : 3, "titulo" : "Pajaritos", "longitud" : "4:15" } ] }
{ "_id" : ObjectId("550f173fc357b7a4e7a46e11"), "tipo" : "CD", "Artista" : "Los piratas", "Titulo" : "Recuerdos", "canciones" : [ { "cancion" : 1, "titulo" : "Adios mi barco", "longitud" : "3:20" }, { "cancion" : 3, "titulo" : "Pajaritos", "longitud" : "4:15" }, { "cancion" : 4, "titulo" : "Pajaritos4", "longitud" : "44:15" } ] }

Solution

In order to get the expected results, I would use the $not operator instead of the $ne operator. The $not operator would pretty match negate $elemMatch and give you the expected results. Your query shall therefore be:

db.media.find({
    "tipo": "CD",
    "canciones": {
        "$not": {
            "$elemMatch": {
                "titulo": "Pajaritos",
                "cancion": 2
            }
        }
    }
})

I hope it helps.

Comments

[styvane]

Do you want only subdocuments in canciones that matches the given criteria or document?

[Ricardo]

@Michael I want the document.

[Ricardo]

I've already try with $not operator and confuse me even more, because is still showing "Pajaritos" but one result less. I updated the question.

[manu2013]

Oh I think I understand a bit more what you trying to achieve. You do not want the results to contain documents with "canciones.titullo" = "Pajaritos" OR documents with "cancans.cancion" = 2. Is that correct?

[Ricardo]

No, I'm trying "not (canciones.titullo=Pajaritos and canciones.cancion=2)"

[manu2013]

In that case, it appears from the sample data you provided that 2 documents should be returned.

[wdberkeley]

Do you want documents where (in the same array element) titullo is not "Parajitos" AND cancion is not 2, or documents where titullo is not 2 OR cancion is not 2. By the DeMorgan laws, not (titullo=Pajaritos and cancion=2) is equivalent to the latter - "not both" is equivalent to "not one or not the other". It is not equivalent to "not one and not the other".