0

Is it possible to use $and and $or logical operators when querying array?

Because when I create a collection with 2 documents

db.inventory.insertMany([
  { item: "planner", qty: 75, tags: ["blank", "red"], dim_cm: [ 25 ] },
  { item: "postcard", qty: 45, tags: ["blue"], dim_cm: [ 10, 50 ] }
]);

and query

db.inventory.find( { dim_cm: {$or: [ {$gt: 49}, {$size: 1} ] }  }  )

the result is error unknown operator $or.

I can rewrite the query as

db.inventory.find( { $or: [{dim_cm: {$gt: 49}}, {dim_cm: {$size: 1}}] } )

but why isn't the first query variant valid?

Radek Micek
  • 447
  • 2
  • 9
  • It isn't valid because that's not the correct syntax for using the `$or` aggregation operator in MongoDB. You can use both the `$and` and `$or` operators together, as long as the correct syntax is used. For example, you could put 2 `$or` statements inside of an `$and`. – Evan Bechtol May 14 '18 at 11:50
  • Because it is not valid and the documentation clearly states it. `$or` does not apply to a "field", but instead is a "list of query conditions" for which "either" can apply as a match. Hence why it's an array. You can "shorthand" for simple values using `$in` i.e `dim_cm: { $in: [10,25] }` selects both documents which match that. But "other expressions" need the array. Honestly, the [documentation `$or` is pretty clear.](https://docs.mongodb.com/manual/reference/operator/query/or/) – Neil Lunn May 14 '18 at 11:51
  • What's still not completely clear to me is the difference between *expression* and *query* which are used in the documentation. Because if I use `$or` the result is a *query*? So I can use `$or` inside `$elemMatch` because it needs a *query*? – Radek Micek May 14 '18 at 12:32

1 Answers1

0

The $or operator performs a logical OR operation on an array of two or more and selects the documents that satisfy at least one of the . The $or has the following syntax:

In the first query you are trying to find dim_cm before your or operator. This clearly in plain text says "Find dim_cm or gt 49, size of the array 1".

Whereas in second query it clearly shows you are trying to find dim_cm between greater than 49, size of the array 1.

For more information Check here.

Tatkal
  • 568
  • 4
  • 9
  • 29
  • There is no "and" logic in OP's queries. The syntax being used incorrect, they essentially say the same thing, but the second query is the correct syntax. What the second query really says is "Find all documents where `dim_cm` is greater than 49, *or* `dim_cm` has a size of 1. – Evan Bechtol May 14 '18 at 11:56
  • @EvanBechtol while trying to explain briefly, I have made mistake in typing. Corrected my answer. – Tatkal May 14 '18 at 11:59