Using Data Query Language (DQL) to query data in the GSE

Introduction

While most of the APIs in the GSE have been designed to provide simple filtering of results suitable for most day to day use cases, it is useful at times to have more flexibility to perform custom queries for specific result sets. The GSE includes a Data Query Language (DQL) which (when enabled for a specific API endpoint) provides a powerful and familiar approach using boolean logic to curate results in a custom manner.

Basic Expression Syntax

DQL is designed to mimic common language constructs for basic field value filtering. In it's simplest form a single clause query which matches a value of 5 on a numeric id field would be supplied as follows:

id == 5

Where fields have string values either single or double quotes can be used to enclose the matching value.

name == 'John Smith'

name == "John Smith"

Inequalities

Inequality clauses can be supplied in a similar vein for greater, less and not equal expressions

id > 5    # id greater than 5

id >= 5   # id greater than or equal to 5

id < 5    # id less than 5

id <= 5   # id less than or equal to 5

id != 5   # id not equal to 5

Null Values

Matching on null and not-null values is achieved using the isnull and isnotnull keywords accordingly

id isnull

id isnotnull

String Matching

DQL supports simple and more advanced functionality for matching within string values. Simple prefix, suffix and contains matches can be achieved using the startswith, endswith and contains keywords respectively

name startswith 'john'    # name begins with the string 'john'

name endswith 'smith'     # name ends with the string 'smith'

name contains 'z'         # name contains the string 'z' 

Wildcard matching within strings is possible using the like keyword and supplying * wildcards within the match string.

name like 'joh*'       # name begins with the string 'joh'

name like '*ith'       # name ends with the string 'ith'

name like '*z*'        # name contains the string 'z'

name like 'an*sm*'     # name starts with 'an' and then contains the pattern 'sm'.

For more advanced matching, standard regular expressions can also be supplied in the match string using the like keyword by enclosing the match string in / characters.

name like '/[A-Z]+/'     # name contains at least one capital letter

name like '/^[a-z]*$/'   # name only contains lower caase letters

NB: if ^ and $ (start and end characters) are not explicitly supplied in the pattern the pattern will be treated as matching anywhere in the string as opposed to the whole string.

Set of Values Matching

Matching on a set of values is possible using the in and notin keywords respectively. In this case, the possible values is supplied using common array syntax enclosed with [ and ].

id in [1,3,5,7]                      # id must be one of values 1,3,5 or 7

id not in [1,3,5,7]                  # id must not be one of values 1,3,5 or 7

name in ['bob','mary','jane']        # name must be one of 'bob','mary' or 'jane'

name not in ["bob","mary","jane"]    # name must not be one of 'bob','mary' or 'jane'

Combining Clauses

DQL allows for the combination of multiple clauses using standard boolean logic and use of brackets for sub expressions.

To create additive clauses (logical AND) you can use the && operator to separate clauses

id > 3 && name like 'john*'

For exclusive clauses (logical OR) you can use the || operator to separate clauses

id > 3 || name like 'john%'

For more advanced expressions you can use brackets to group expressions in the usual way.

(id > 3 && name like 'john%') || (id < 3 && name like 'andrew%')

(date > '2025-01-01' || (id < 300 and status = 'Active'))

NB: You cannot supply different logic types within the same bracket scope to avoid ambiguous logic issues. Nested brackets should be used to avoid this. As an example the following expression would be invalid.

name == 'mark' || id = 3 && status = 'Active'