MongoDB : Query documents by values in array-CodePudding

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 ?

CodePudding user response：

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.

CodePudding user response：

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]}}}])