Mongodb find documents with a specific aggregate value in an array-CodePudding

I have a mongo database with a collection of countries. One property (currencies) contains an array of currencies. A currency has multiple properties:

"currencies": [{
        "code": "EUR",
        "name": "Euro",
        "symbol": "€"
    }],

I wish to select all countries who use Euro's besides other currencies. I'm using the following statement:

db.countries.find({currencies: { $in: [{code: "EUR"}]}})

Unfortunately I'm getting an empty result set.

When I use:

db.countries.find({"currencies.code": "EUR"})

I do get results. Why is the first query not working and the second one succesfull?

CodePudding user response：

The first query is not working as it checks whether the whole currency array is in the array, which is never true.

It is true when:

currencies: { 
  $in: [
    [{
        "code": "EUR",
        "name": "Euro",
        "symbol": "€"
    }],
    ...
  ]
}

I believe that $elemMatch is what you need besides the dot notation.

db.collection.find({
  currencies: {
    $elemMatch: {
      code: "EUR"
    }
  }
})

Sample Mongo Playground

CodePudding user response：

MongoDB works in the same way if the query field is an array or a single value, that's why the second one works. So why the first one doesn't work? The problem here is that you are looking for an object that is exactly defined as {code: "EUR"}: no name or symbol field are specified. To make it work, you should change it to:

db.getCollection('countries').find({currencies: { $in: [{
            "code" : "EUR",
            "name" : "Euro",
            "symbol" : "€"
        }]}})

or query the subfield directly:

db.getCollection('stuff').find({"currencies.code": { $in: ["EUR"]}})