where is used to filter-out the documents from final results.
Operator: >= <= <> != < > = ==
The operators above are considered basic and self-explanatory. They work on all value types including 'numbers' and 'strings'.
The simplest example would be to return results with the field value equal to a given input. If you want to return a document from the
@companies collection (more about collection can be read here), you need to execute the following query:
where Name = 'The Big Cheese'
Filtering on nested properties is also supported, so in order to return all companies from 'Albuquerque' we need to execute following query:
where Address.City = 'Albuquerque'
between returns results inclusively, and the type of border values used must match. It works on both 'numbers' and 'strings' and can be substituted with the
<= operators (see the example below).
where PricePerUnit between 10.5 and 13.0
where PricePerUnit >= 10.5 and PricePerUnit <= 13.0
in is validating if a given field contains passed values. It will return results if a given field matches any of the passed values.
where Name IN ('The Big Cheese', 'Unknown company name')
where Lines.ProductName in ('Chang', 'Spegesild', 'Unknown product name')
Operator: ALL IN
This operator checks if all passes values are matching a given field. Due to its mechanics, it is only useful when used on array fields.
The following query will yield no results in contrast to an 'in' operator.
where Lines.ProductName all in ('Chang', 'Spegesild', 'Unknown product name')
but removing the 'Unknown product name' will give you orders that only contains products with both 'Chang' and 'Spegesild' names
where Lines.ProductName all in ('Chang', 'Spegesild')
Binary operators: AND OR NOT
Binary operators can be used to build more complex statements. The
NOT operator can only be used with one of the other binary operators creating
OR NOT or
AND NOT ones.
where Name = 'The Big Cheese' OR Name = 'Richter Supermarkt'
where Freight > 500 AND ShippedAt > '1998-01-01'
where Freight > 500 AND ShippedAt > '1998-01-01' AND NOT Freight = 830.75
Subclauses: ( )
Subclauses can be used along with binary operators to build even more complex logical statements. They are self-explanatory so no example will be given.