'MongoDB : Query documents by values in array

I have those documents :

{"_id" : 1, "actors" : [{"matricule" : "AVB", "name":"XXX"}, {"matricule" : "AVB", "name":"YYY"}]}
{"_id" : 2, "actors" : [{"matricule" : "PMH", "name":"FFF"}, {"matricule" : "BNG", "name":"HHH"}]}

I would like to get only the first document because it's the same matricule but a different name.

I use this query :

{
 $expr:{$eq:["$actors.0.matricule", "$actors.1.matricule"]}
}

But it doesn't work, I don't have any results. Do you know why ?

mongodbmongodb-query


Solution 1:[1]

Try to use $arrayElemAt

db.collection.find({
  $expr: {
    $eq: [
      {
        "$arrayElemAt": [
          "$actors.matricule",
          0
        ]
      },
      {
        "$arrayElemAt": [
          "$actors.matricule",
          1
        ]
      }
    ]
  }
})

Here is the Mongo playground for your reference.

Solution 2:[2]

Query

  • "$actors.matricule" is an array of all those matricule values
  • make it a set (union with the empty array)=> no duplicates
  • filter to keep only those with size 1 (all have the same value)

*can work if you have one or many members inside the actors array

Playmongo

aggregate(
[{"$match": 
   {"$expr": 
     {"$eq": [{"$size": {"$setUnion": ["$actors.matricule", []]}}, 1]}}}])

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 ray
Solution 2 Takis

Related Questions