Where

At its most basic, Where is used to get data that fulfills a specified condition.
For most queries, we do this by specifying the xpath of an element in root.

Where can be combined with operators to get results based on more than one condition.
Multiple column names are separated by a comma ,.

Operator	What it does
and	Returns data if all the conditions separated by And are TRUE
or	Returns data if all the conditions separated by Or are TRUE
not	Returns data if the condition(s) is NOT TRUE
in	Specifies multiple values, short for multiple Or conditions
between	Selects a range, start and end values included (values can be numbers, text, or dates)
like	Search for a specified pattern, case sensitive
ilike	Search for a specified pattern, case insensitive

For Like and iLike, there are two wildcard options:

• The percent sign % for multiple characters.
• The underscore sign _ for a single character.

Use	What it does
like a%	Finds data that starts with "a"
like %a	Finds data that ends with "a"
like %aa	Finds data that has "aa" within it
like _a%	Finds data that has "a" as the second character
like a_%	Finds data that starts with "a" and is at least 2 characters in length
like a__%	Finds data that starts with "a" and is at least 3 characters in length
like a%s	Finds data that starts with "a" and ends with "s"

Examples

where root='/iati-activities/iati-activity' 

where root='/iati-activities/iati-activity/other-identifier' and xson->>'@type' = 'B1'

where root in ('/iati-activities/iati-activity/participating-org', '/iati-activities/iati-activity/transaction/')