DDSQL Expressions and Operators

Docs > DDSQL Editor > DDSQL Expressions and Operators

Cette page n'est pas encore disponible en français, sa traduction est en cours.
Si vous avez des questions ou des retours sur notre projet de traduction actuel, n'hésitez pas à nous contacter.

Join the Preview!

DDSQL is in Preview.

Request Access

Value expressions are the general expression language used to produce values for conditions, SELECT expressions, filters, and clauses like WHERE, ORDER BY, and GROUP BY. The expression syntax of DDSQL is a superset of SQL expression syntax.

Arithmetic operators

DDSQL supports standard binary and unary infix arithmetic notation from SQL and many other languages:

Operator	Description	Example	Result
+	addition	2 + 3	5
-	subtraction	2 - 3	-1
*	multiplication	2 * 3	6
/	division (non-truncating)	5 / 2	2.5

The standard order of operations applies. To control the order of operations, add parentheses: (5 - 2) * 3.

Comparison operators

DDSQL implements the following comparison operators:

Operator	Description	Example	Result
>	greater than	2 > 3	false
<	less than	2 < 3	true
>=	greater than or equals	3 >= 2	true
<=	less than or equals	3 <= 2	false
=	equals*	3 = 3	true
!=, <>	not equals	3 != 3	false

For tag references and tag groups, the equality operator (=) is treated as a “contains” comparison. See the Querying Tags in DDSQL for more details.

SQL comparison keywords

DDSQL supports the following SQL keywords, which function as standard Boolean operators:

Operator	Description	Example	Result
`NOT`	Filter records based on more than one condition.	`SELECT * FROM host WHERE NOT env = 'prod';`	Return all hosts that are not in the prod environment.
`AND`	Filter records based on more than one condition.	`SELECT * FROM host WHERE env = 'prod' AND cloud_provider = 'aws';`	Return all hosts that are in the prod environment and the AWS cloud provider.
`OR`	Filter records based on more than one condition.	`SELECT * FROM host WHERE env = 'prod' AND cloud_provider = 'aws';`	Return all hosts that are either in the prod environment or the aws cloud provider.

DDSQL also supports the following comparator keywords as they are defined in the SQL standard:

Operator	Description	Example	Result
`IS NULL`	Select rows if the specified field is null.	`SELECT * FROM host WHERE cloud_provider IS NULL;`	Return all rows that contain no data in the `cloud_provider` column.
`IS NOT NULL`	Select rows if the specified field is not null. Exclude rows with missing data.	`SELECT * FROM host WHERE cloud_provider IS NOT NULL;`	Return all rows that contain data in the `cloud_provider` column.
`LIKE`	Search for a specific pattern in a string value. You can use the following wildcard characters to define the patterns: Percent sign (%): Represents zero, one, or multiple characters. Underscore (_): Represents a single character.	`SELECT * FROM aws_eks_cluster WHERE LOWER(logging) LIKE '%"enabled":true%';`	Return all rows from the `aws_eks_cluster` table where the `logging` column is `"enabled":true`.
`NOT LIKE`	Exclude rows from a search, where the row has a specific pattern in a string value. You can use the wildcards `%` and `_` for pattern matching.	`SELECT * FROM aws_eks_cluster WHERE LOWER(logging) NOT LIKE '%"enabled":true%';`	Return all rows from the `aws_eks_cluster` table where the `logging` does not have `"enabled":true%'`.
`IN`	Find multiple values in a `WHERE` clause. The `IN` operator is shorthand for multiple `OR` conditions.	`SELECT * FROM host WHERE cloud_provider IN ('aws', 'gcp');`	Return all rows from `host` table where the `cloud_provider` value is either ‘aws’ or ‘gcp’.
`NOT IN`	Replace a set of arguments with the `<>` or `!=` operator that is combined with the `AND` operator	`SELECT * FROM host WHERE cloud_provider NOT IN ('aws', 'gcp');`	Return all rows where `cloud_provider` is not `aws` or `gcp`.

DDSQL supports the BETWEEN keyword such that a BETWEEN x AND y is equivalent to a >= x AND a <= y. See the Postgres documentation for BETWEEN for details.

Logical operators

Name	Description
AND	Boolean logic, a & b
OR	Boolean logic, a \|\| b
XOR	Boolean logic, a ^ b
NOT	Boolean logic, !a
IS NULL	Returns true for each row that is null

CASE

The CASE expression is a generic conditional expression, similar to if/else statements in other programming languages. CASE comes in two forms, simple and searched.

Simple CASE statements

Simple CASE statements use the following syntax:

CASE expression
  WHEN value THEN result
  [ WHEN ... ]
  [ ELSE result ]
END

The expression is computed, then compared to each of the value expressions in the WHEN clauses until one is found that is equal to it. If no match is found, the result of the ELSE clause, or NULL if ELSE is omitted, is returned.

Searched CASE statements

Searched CASE statements use the following syntax:

CASE
  WHEN condition THEN result
  [ WHEN ... ]
  [ ELSE result ]
END

If a condition’s result is true, the value of the CASE expression is the result that follows the condition, and the remainder of the CASE expression is not processed. If the condition’s result is not true, any subsequent WHEN clauses are examined in the same manner. If no WHEN condition yields true, the value of the CASE expression is the result of the ELSE clause. If the ELSE clause is omitted and no condition is true, the result is NULL.

CAST

CAST specifies a conversion from one data type to another.

Syntax

CAST(expression AS type)

Not all types are convertible in this way.

DDSQL also supports Postgres casting syntax:

expression::type

For example, SELECT 1::text;.