Where - notshi/dquery GitHub Wiki
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/')