CODEWITHSUNDEEP
mongodbmongodb-query
stenci
stenci

Reputation: 8481

Query by field value, not value in field array

The following snippet shows three queries:

I was not able to find the documents containing a field a containing the string "x", not inside an array.

> db.stuff.find({},{_id:0})
{ "a" : "x" }
{ "a" : [ "x" ] }
> db.stuff.find({a:"x"},{_id:0})
{ "a" : "x" }
{ "a" : [ "x" ] }
> db.stuff.find({a:{$elemMatch:{$eq:"x"}}},{_id:0})
{ "a" : [ "x" ] }
>

Upvotes: 1

Views: 106

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151112

MongoDB basically does not care if the data at a "given path" is actually in an array or not. If you want to make the distinction, then you need to "tell it that":

db.stuff.find({ "a": "x", "$where": "return !Array.isArray(this.a)" })

This is what $where adds to the bargain, where you can supply a condition that explicitly asks "is this an array" via Array.isArray() in JavaScript evaluation. And the JavaScript NOT ! assertion reverses the logic.

An alternate approach is to add the $exists check:

db.stuff.find({ "a": "x", "a.0": { "$exists": false } })

Which also essentially asks "is this an array" by looking for the first element index. So the "reverse" false case means "this is not an array".

Or even as you note you can use $elemMatch to select only the array, but "negate" that using $not:

db.stuff.find({ "a": { "$not": { "$elemMatch": { "$eq": "x" } } } })

Though probably "not" the best of options since that also "negates index usage", which the other examples all strive to avoid by at least including "one" positive condition for a match. So it's for the best to include the "implicit AND" by combining arguments:

db.stuff.find({
  "a": { "$eq": "x", "$not": { "$elemMatch": { "$eq": "x" } } } 
})

Or for "aggregation" which does not support $where, you can test using the $isArray aggregation operator should your MongoDB version ( 3.2 or greater ) support it:

db.stuff.aggregate([
  { "$match": { "a": "x" } },
  { "$redact": {
    "$cond": {
      "if": { "$not": { "$isArray": "$a" } },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }}
])

Noting that it is good practice to supply "regular" query conditions as well where possible, and in all cases.

Also noting that querying the BSON $type does not typically work in this case, since the "contents" of the array itself are in fact a "string", which is what the $type operator is going to consider, and thus not report that such an array is in fact an array.

Upvotes: 1

Related Questions