Operators and Conditionals¶

TQL and TQLFlow support multiple DataTypes and the creation of Simple and Complex Conditionals to help you with querying data.

—¶

Conditional¶

Conditionals are used in a few different clauses for TQL and TQLFlow. The first is in the WHERE clause another is in the JOIN and they have some uses in SELECT functions.

All Datatypes and their associated functions are usable in most conditionals. Some restrictions for certain operators do exists but are noted below.

(Skip to Examples of Conditions in WHERE and JOIN clauses).

Basic Condition¶

At its basic element a condition is an attribute (sometimes referred to as the left-hand side), an operator, and usually a constant value (sometimes referred to as the right-hand side). This is called a predicate. For example:

first_name = 'John'

There are 2 operators that do not have a right-hand side (ISNULL and ISNOTNULL), for example:

first_name ISNOTNULL

The available Operators you can use when creating a predicate:

Operators:

Operator	Description
`=`	Equal
`>`	Greater than
`<`	Less than
`>=`	Greater than or equal
`<=`	Less than or equal
`<> or !=`	Not Equal
`LIKE`	Search for a pattern (case-sensitive)
`ILIKE`	Search for a pattern (case-insensitive)
`NOTLIKE`	Search for a pattern (case-sensitive) that does NOT match
`NOTILIKE`	Search for a pattern (case-insensitive) that does NOT match
`REGEX`	Searches for with a regex pattern
`IN`	To specify multiple possible values for an attribute
`NOTIN`	To specify multiple possible values for an attribute which NONE match
`ISNULL`	Selects when the attribute does not have value; does not have a right-hand side
`ISNOTNULL`	Selects when the attribute has a value; does not have a right-hand side

Complex Condition¶

A Condition can become more complex and may contain more than one predicate; to do that the user must supply a Conjunctive between the predicates, for example:

first_name = 'John' AND last_name = 'Doe'

A more complex condition is when predicates are grouped using parenthesis. This follows a mathematical order of precedence, therefore anything within parenthesis is performed before the next level of outer parenthesis, if any. For example:

first_name = 'John' AND (last_name = 'Doe' OR last_name = 'Smith')

In that example, the evaluation is usually left-to-right. But the parenthesis require the evaluator to find all last names that are Doe OR Smith AND the first name has to be John. Example results could be: John Smith, John Doe. But NOT: Steve Smith.

Conjunctives:

Conjunctive	Description
`AND`	Displays a record if all the conditions separated by AND are TRUE
`OR`	Displays a record if any of the conditions separated by OR is TRUE

—¶

Conditional Examples¶

<value> is a placeholder for using any Datatype in the form of a Literal, Attribute or Function <value-list> is a place holder for a list of values.

WHERE clause example¶

WHERE clause with one condition:

WHERE <condition>

WHERE name = "John Smith"

In this example the predicate is name = "John Smith" and would match on any document that has an attribute name with the value “John Smith”.

Operator Examples¶

Equals¶

This value can be used for all the data types that Tql and TqlFlow support. For matching on a string the value needs to be surrounded by parenthesis like in the example.

WHERE <value> = <value>

This example will match on any documents where the name is ‘John Smith’

WHERE name = 'John Smith'

—¶

Greater Than¶

This can be used on Numeric types and Date types

WHERE <value> > <value>

This will match on any document where the age value is greater than 5.

WHERE age > 5

—¶

Less Than¶

This can be used on Numeric types and Date types

WHERE <value> < <value>

This will match on any document where the age value is less than 5.

WHERE age < 5

—¶

Greater Than or Equal¶

This can be used on Numeric types and Date types

WHERE <value> >= <value>

This will match on any document where the age value is greater than 5 or equal to 5.

WHERE age >= 5

—¶

Less Than or Equal¶

This can be used on Numeric types and Date types

WHERE <value> <= <value>

This will match on any document where the age value is less than 5 or equal to 5.

WHERE age > 5

—¶

Not Equal¶

This value can be used for all the data types that Tql and TqlFlow support. For matching on a string the value needs to be surrounded by parenthesis like in the example.

WHERE <value> != <value>

This example will match on any documents where the name is not ‘John Smith’

WHERE name != 'John Smith'

—¶

LIKE¶

Search for a pattern (case-sensitive)

WHERE <value> LIKE <value>

This will match on any names that are only ‘John’. This is case-sensitive, it will not match on ‘john’.

WHERE name LIKE 'John'

The example above won’t do a partial match, if you are looking for a name all names that are John followed by a last name (‘John Smith’, ‘John Doe’). You can use the ‘%’ symbol to not if the match is exact, at the beginning, at the end of in the middle.

WHERE name LIKE 'John%'

WHERE name LIKE '%Smith'

WHERE name LIKE '%ohn Smi%'

These will all match on ‘John Smith’ and each shows a different way to use ‘%’ with the LIKE operator.

—¶

ILIKE¶

This returns documents that match the specific value. (case-sensitive)

WHERE <value> ILIKE <value>

This will match on ‘John’ with any form of capitalization (‘John’, ‘john’, ‘JoHN’)

WHERE name ILIKE 'John'

ILIKE Can also use the % symbol to handle partial matching. The examples below will all match on ‘John Smith’.

Some of the examples below match using %, while some show the case-insensitive matching.

WHERE name ILIKE 'john%'

WHERE name ILIKE '%smith'

WHERE name ILIKE '%ohn smi%'

WHERE name ILIKE 'JOHN SMITH'

WHERE name ILIKE 'john SMITH'

—¶

NOTLIKE¶

This returns documents that do NOT match the specific value. (case-sensitive)

WHERE <value> NOTLIKE <value>

This will match on any names not ‘John’, this is case-sensitive, this would return a document if name is ‘john’.

WHERE name NOTLIKE 'John'

NOTLIKE allows % in the value, this allows for partial matching for case-sensitive NOTLIKE matching.

WHERE name NOTLIKE 'John%'

WHERE name NOTLIKE '%Smith'

These two examples do slightly different things, the first example will return any document where the name does not start with ‘John’.

The second example will return any document where the name does not end with ‘Smith’.

—¶

NOTILIKE¶

This returns documents that do not match the value, this is case-insensitive.

WHERE <value> NOTILIKE <value>

This would return anything that is not ‘john’, ‘John’, ‘JOhn’…

WHERE name NOTILIKE 'John'

NOTILIKE allows for % in the value for partial matching with NOTILIKE.

WHERE name NOTILIKE 'john%'

WHERE name NOTILIKE '%smith'

These two examples are similar to the ones shown in NOTLIKE, the first returns any document where the name does NOT start with ‘john’ or any capitalization form because we use the case-insensitive NOTILIKE.

The second example matches on any document where name does not end in ‘smith’ or any capitalization of ‘smith’.

—¶

REGEX¶

Searches for with a regex pattern

WHERE <value> REGEX <regex>

The below example shows how you can use regex to match on documents. This doesn’t get into writing specific regex matching use cases because Regex contains a lot of ways to match and is a very powerful.

This specific example shows how you can use the [a-z] Regex matching, this specific example matches on any lowercase letter a-z.

WHERE name REGEX '[a-z]'

—¶

IN¶

To specify multiple possible values for an attribute

WHERE <value> IN <value list>

This example returns documents where name is ‘John’ or ‘Jane’

WHERE name IN ('John', 'Jane')

You can also use list functions:

WHERE name IN APPEND(['John', 'Jane'], some_list)

As well as list literals:

WHERE name IN ['John', 'Jane']

—¶

NOTIN¶

To specify multiple possible values for an attribute which NONE match.

WHERE <value> NOTIN <value-list>

This example returns documents where name is NOT ‘John’ or ‘Jane’

WHERE name NOTIN ('John', 'Jane')

You can also use list functions:

WHERE name NOTIN APPEND(['John', 'Jane'], some_list)

As well as list literals:

WHERE name NOTIN ['John', 'Jane']

—¶

ISNULL¶

Selects when the attribute does not have value; does not have a right-hand side

WHERE <value> ISNULL

This example query returns documents where the name attribute is missing or null.

WHERE name ISNULL

—¶

ISNOTNULL¶

Selects when the attribute has a value; does not have a right-hand side

WHERE <value> ISNOTNULL

This example query returns documents where the name attribute is not null or missing.

WHERE name ISNOTNULL

JOIN clause example¶

JOIN clause with one condition

JOIN <collection> AS <alias> ON <condition>

JOIN test_collection AS tc ON tc.name = 'John Smith'