Syntax

Symbol Conventions

In this section, the words that need to be typed in the original form are capitalized, and the characters that need to be typed in the original form are enclosed in single quotation marks (').

'[x]' indicates that statement 'x' can be used once or not even once.

'(x)' indicates that statement 'x' is a whole. '(x, ...)' indicates that statement 'x' can be used once or multiple times. If statement 'x' is used multiple times, use commas (,) to separate them.

'|' indicates all possible alternatives.

'expression' indicates any expression. Specially, 'bool_expression' indicates any Boolean expression.

'identifier' indicates a valid identifier. An identifier can contain letters, digits, and underscores (_), and cannot start with a digit.

'column_name' indicates a valid field name. It can be 'identifier' or multiple identifiers, for example,'A.id'.

'table_name' indicates a valid table name. In the ResourceQL syntax, 'table_name' must be 'resources'.

A unit enclosed in double quotation marks ("") is considered as a whole. For example, to indicate a column name containing special characters, add double quotation marks ("") before and after the column name.

Basic Query Syntax

[WITH (with_item, ...)]
SELECT [DISTINCT | ALL] (select_item, ...)
[FROM (from_item, ...)]
[WHERE bool_expression]
[GROUP BY [DISTINCT | ALL] (expression, ...)]
[HAVING booleanExpression]
[ORDER BY (expression [ASC | DESC] [NULLS (FIRST | LAST)], ...)]
[LIMIT number]

Numeric Operation and Boolean Operation

ResourceQL supports binary mathematical operations on integers and floating digits. The following operators are supported: '+,-,*,/,%'

Values of the same type can be compared. The following comparison operators are supported: <, >, <=, >=, =, <>, !=. Both <> and != indicate not equal. Values are compared in size, and strings are compared in lexicographic order. Values and sets can also be compared. In this case, one from 'ALL | SOME | ANY' on the right of the comparison operator is used to specify the comparison range. 'All' indicates that all elements in the set must be met. 'SOME/ANY' indicates that at least one element must be met.

expression ('=' | '<>' | '!=' | '<' | '>' | '<=' | '>=') 
expression
expression ('=' | '<>' | '!=' | '<' | '>' | '<=' | '>=') 
[ALL | SOME | ANY] '(' query ')'

'bool_expression' indicates any Boolean expression. (True or False is returned after the operation.) 'bool_expression' includes the following syntax:

NOT bool_expression
bool_expression (AND | OR) bool_expression
expression [NOT] BETWEEN expression AND expression
expression [NOT] IN '(' query ')'
EXISTS '(' query ')'
expression [NOT] LIKE pattern [ESCAPE escape_characters]
expression IS [NOT] NULL
expression IS [NOT] DISTINCT FROM expression

In particular, operator '||' concatenates the left and right values and returns a new value. The left and right values are of the same type: array or string.

Timestamp

ResourceQL allows you to query fields of the time type. The query result is converted to the zero time zone and returned in ISO Date format. The result is saved in milliseconds.

Time types can be connected by comparison operators. If you want to use a literal to indicate time, use timestamps to write 'time'. 'time' can be in any ISO date format or a common time format. The following formats are allowed:

2019-06-17T12:55:42.233Z

2019-06-17T12:55:42Z

2019-06-17 12:55:42

2019-06-17T12:55:42.00 + 08:00

2019-06-17 05:55:40 - 06:00

2019-06-17

2019

If the time zone is not added, the zero time zone is used by default. If the 24-hour time is not added, 0:00 is used by default. If the month is not added, January 1 is used by default.

For example, to sort resources created since 12:55:00 on September 12, 2020 by update time in descending order, run the following statement:

select name, created, updated from resources 
where created >= timestamp '2020-09-12T12:55:00Z' 
order by updated DESC

Fuzzy Search

string LIKE pattern [ESCAPE escape_characters]

'LIKE' is used to determine whether a character string complies with a pattern. If you want to express the literal of '%' and '_' in the pattern, you can specify an escape character (for example, '#') after ESCAPE and write '# %' and '#_' in the pattern.

Wildcard '%' indicates that zero or multiple characters are matched.

Wildcard '_' indicates that one character is matched.

The fuzzy query of OBS buckets can be written in the following format:

SELECT name, id FROM resources 
	WHERE provider = 'obs' AND type = 'buckets' AND name LIKE '%figure%'

or

SELECT name, id FROM resources 
	WHERE provider = 'obs' AND type = 'buckets' AND name LIKE '%figure#_%' ESCAPE '#'

Condition Functions

The return value of CASE varies according to the actual situation. CASE can be used in either of the following ways:

• Calculate the value of a given expression and return the corresponding result based on the value.
• Calculate the value of each bool_expression in sequence, finds the first expression that meets the requirements, and returns the result.

CASE expression
    WHEN value1 THEN result1
    [WHEN value2 THEN result2]
    [...]
    [ELSE result]
END
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    [...]
    [ELSE result]
END

IF can be used in either of the following ways:

• 'IF(bool_expression, value)': If the bool_expression value is true, 'value' is returned. Otherwise, NULL is returned.
• 'IF(bool_expression, value1, value2)': If the Boolean expression value is true, 'value1' is returned. Otherwise, 'value2' is returned.

Using Functions to Simplify Queries

ResourceQL provides a variety of functions to simplify queries. For details about the functions, see Functions.

ResourceQL supports lambda expressions. The arguments of some functions may be another function. In this case, it is convenient to use the lambda expression.

For example, to list the ECSs and the EVS disks attached to each ECS, run the following SQL statement:

SELECT ECS.id AS ecs_id, EVS.id AS evs_id FROM
	(SELECT id, transform(properties.ExtVolumesAttached, x -> x.id) AS evs_list 
	FROM resources WHERE provider = 'ecs' AND type = 'cloudservers') ECS
    (SELECT id FROM resources WHERE provider = 'evs' AND type = 'volumes') EVS
    WHERE contains(ecs.evs_list, evs.id)

'contains(a, element)→boolean' determines whether an element appears in array a.

'transform(array(T), function(T, S))→array(S) can convert an array of a certain type into an array of another type.

Join and Unnest

ResourceQL supports 'JOIN' and 'UNNEST'. 'JOIN' can be classified into the following types:

• [INNER] JOIN
• LEFT [OUTER] JOIN
• RIGHT [OUTER] JOIN
• FULL [OUTER] JOIN

'JOIN' must be followed by 'USING(...)' or 'ON <bool_expression>'.

'USING' is used to specify the names of columns to join.

'ON' accepts a Boolean expression and merges values of 'JOIN' if the Boolean expression value is true. To ensure performance, there must be at least one equation in a Boolean expression in the conjunctive normal form (CNF), and the operation content at the left and right ends of the equation is provided by the left and right tables separately.

You can add 'NATURAL' before 'JOIN' to indicate a connection. In this case, you do not need to add 'USING' or 'ON' after 'JOIN'.

'UNNEST' can unpack an array into a table. With 'WITH ORDINALITY', there is an auto-increment column. The format is as follows:

table_name CROSS JOIN UNNEST '(' (expression, ...) ')' [WITH ORDINALITY]

Note that 'CROSS JOIN' can only be used to connect to 'UNNEST'. ResourceQL does not support 'CROSS JOIN' in other formats.

The preceding example of querying the association between an ECS and an EVS disk can also be written in the following format:

SELECT ECS_EVS.id AS ecs_id, EVS.id AS evs_id FROM 
	(SELECT id, evs_id FROM (SELECT id, transform(properties.ExtVolumesAttached, x ->x.id) AS evs_list
         FROM resources WHERE provider = 'ecs' AND type = 'cloudservers') ECS 
     CROSS JOIN UNNEST(evs_list) AS t (evs_id)) ECS_EVS, 
     (SELECT id FROM resources WHERE provider = 'evs' AND type = 'volumes') EVS 
     WHERE ECS_EVS.evs_id = EVS.id