If you can't find the product or version you're looking for, visit support.ataccama.com/downloads

Search using AQL Expressions

Overview

AQL aims at covering basic search and filtering functionality through easy-to-write conditions.

Every listing in AQL can be narrowed down by providing a filter. Its usage boils down to identifying a property and applying a condition on it. The condition does not specify which entity it is used for; that information comes from the context.

For example, if we want to filter sources whose names contain postgres as a substring, we could use the following query:

query {
  sources(
    filter: "name like 'postgres'"
    versionSelector: {draftVersion: true}
  ) {
    totalCount
  }
}

You can think of these filters as an SQL WHERE clause. While the syntax resembles that of SQL in the case of the easiest conditions, AQL is more powerful when it comes to placing conditions on related entities as well.

For example, if you want to filter sources whose names contain a substring postgres and are referenced by at least two catalog items that each contain an attribute of data type STRING, you could use the following:

query {
  sources(
    filter: "name like 'postgres' and @catalogItem (source).count(attributes.any(dataType = 'STRING') ) > 2"
    versionSelector: { draftVersion: true }
  ) {
    totalCount
  }
}

Or, if you want to find an entity with a specific ID:

query {
  sources(
    filter: "$id = '#gid#'"
    versionSelector: {draftVersion: true}
  ) {
    totalCount
  }
}

/*This AQL query uses a semi-expression.
A semi-expression is a pattern, into which a result of a ONE Runtime expression is inserted.
In this case, "#id#" is a semi-expression, which is evaluated by ONE Runtime as the value of the column `gid`, for example, `813adb4c-7746-49ce-gv46-a00d2454432e``, and the value of the semi-expression is translated to `$id` and used as a filter.*/

Combining conditions

Multiple simpler conditions can be expressed at the same time using one of logical operators:

NOT (alias: !) - Requires the condition not to be satisfied.
AND (&, &&) - Requires both conditions to be satisfied.
OR (|, ||) - Requires at least one condition to be satisfied.
Example 1. Examples on sources
- name like "mysql" and profilingLimitAbsoluteMax > 100
- name like "mysql" or profilingLimitAbsoluteMax < 100

The precedence of the logical operators is as noted but can be altered by using parentheses. Parentheses can be freely put around any condition.

For example, if you have the following conditions:

a is 1 and not b is 2 or c is 3
(a is 1) and not (b is 2 or c is 3)
a is 1 and (not (b is 2) or (c is 3))

Let’s see what would happen if you applied them on the following data:

a = 0, b = 0, c = 0 - Matched by none.
a = 0, b = 2, c = 3 - Matched by 1.
a = 1, b = 2, c = 3 - Matched by 1, 3.
a = 1, b = 0, c = 0 - matched by 1, 2, 3.

Nullability

Null means that a value is not present, which is different from a situation when it is, for example, an empty string (""), zero (0), or false.

You can test the nullability of these types of properties:

Scalar
Single embedded
Reference

Example 2. Examples

propertyName IS NULL
propertyName IS NOT NULL
name is not null (for example, on sources)
profilingResult is not null (on catalog items, single embedded)
source is not null (on catalog items, reference)

There are other equality and inequality operators, see the following sections.

Scalar property conditions

Scalar conditions can test whether a value equals a constant, is greater than a constant, or looks like a constant. They can also be used with the count aggregation operator.

Currently, it’s not possible to have an expression on the right side of a condition.

Scalar conditions can also refer to scalar system properties: * $id - STRING property of the entity’s GID. * $fulltext - Combines all properties into a single STRING. * $path - STRING property of the entity’s entity path.

Primitive values (constants)

A constant value used in scalar conditions can be of type:

Boolean - Possible values are true and false (case-insensitive).
Number - Must be JSON5-compatible.
- Positive and negative: Infinity, NaN (case-sensitive).
- Hexadecimal integer: 0x0, 0XBA0BAB, 0xc0c0a.
- Decimal integers: 0, 42, -175.
- Decimal real numbers: 5.05, -17.666, 15., .5.
- Decimal real numbers with exponent: 15.5e3, 5e-2.
String - Must be JSON5-compatible:
- Single-quoted: 'cat', 'single'quote', 'with new\nline'.
- Double-quoted: "dog", "double\"quote", "with\ttab".

The range of numbers is not checked by the syntax, but it is checked by the receiving scalar type.

Binary operators

You can compare a value of a scalar property using common comparison operators:

Equal = (alias: is, eq, ==).
Not equal != (is not, ne, neq, <>).
Less than < (lt).
Greater than > (gt).
Less than or equal <= (le, lte).
Greater than or equal >= (ge, gte).
Like like (match, ~).

Not all operators can be used for all scalar data types:

Equal and not equal work for all types.
Less than, greater than, and their equal variants work for INTEGER, LONG, FLOAT, DOUBLE.
Like works for STRING. Currently, it searches for a substring and it is case-insensitive.

Example 3. Examples

booleanProperty IS TRUE
longProperty > 5
stringProperty LIKE 'prop'
$id = "0e40b980-d671-4021-876f-a5dbb3d2052c"

Example 4. Examples on sources

profilingLimitEnabled is true
profilingLimitAbsoluteMax = 100
name == "mysql aws"
$fulltext like "party"

In list operator

To search for a value that is in a list or not, use an IN operator.

The list of values must contain at least one value. All values in the list must be either of type Boolean, number, or string.

Example 5. Examples

stringProperty IN ('dog', 'cat')
longProperty NOT IN (2, 3, 5, 7)
name in ("mysql aws", "postgres local") (on sources)

Combining scalar conditions

You can combine multiple conditions on the same scalar property using logical operators and parentheses. This is equivalent to distributing the property to all conditions.

Scalar conditions cannot be combined with nullability conditions. In general, we recommend using scalar conditions sparingly as the feature can be confusing.

Example 6. Examples

longProperty > 5 and < 10 is equivalent to longProperty > 5 and longProperty < 10.
stringProperty (like 'burger' or 'sandwich') and not like 'cheese' is equivalent to (stringProperty like 'burger' or stringProperty like 'sandwich') and not stringProperty like 'cheese'.

Single property conditions

It is possible to make conditions on a single embedded, single reference, or parent entity property, and combine them with other conditions.

The full syntax is:

singleEmbeddedProperty { nestedCondition }
referenceProperty { nestedCondition }
$parent { nestedCondition }

The part between the brackets can be any condition described in this article applied to the respective entity.

If the nested condition does not need any combining operators, you can use the shorthand syntax:

singleEmbedded.longProperty = 2
singleReference.booleanProperty IS TRUE
$parent.stringProperty = 'x'

Take into account the following: * Single embedded and reference properties can be tested for nullability, the parent entity ones cannot. * The long and shorthand syntaxes can be combined: deep.deeper.deeperer { deepest.booleanProperty IS FALSE }. * $parent is currently the only single system property (starting with a dollar sign ($)).

Example 7. Examples

$parent { name like "party" & numberOfProfiledRecords > 100} and dataType = 'STRING' (on catalog item attributes)
source.$fulltext like "m" (on catalog items)
source.name like "mysql"

Array property conditions

Queries for array embedded properties and back references can also include conditions. The nested condition is aggregated and the number of matching entities is evaluated.

Similarly as for single properties, there are two possible syntaxes:

arrayEmbeddedProperty{ aggregation( nestedCondition ) } - Allows to combine multiple aggregation conditions on one array property.
arrayEmbeddedProperty.aggregation( nestedCondition ) - Requires only one aggregation.

Aggregation operators

The following operators can be used in aggregations:

all( nestedCondition ) - Requires a nested condition. All entities must be matched.
none( nestedCondition ) - Requires a nested condition. No entities must be matched.
any( nestedCondition ), some( nestedCondition ) - The nested condition is optional. Requires at least one matching entity.
count( nestedCondition ) scalarCondition - The nested condition is optional. The number of required matched entities is tested with a scalar condition.

Array properties cannot be tested for nullability. If the array is empty (that is, it contains no nested entities), the all operator is true, and the number of matching entities is the same as the number of all entities.

Example 8. Examples

arrayEmbeddedProperty.all( booleanProperty IS TRUE )
arrayEmbeddedProperty.none( stringProperty IS NULL )
arrayEmbeddedProperty .some( longProperty > 100 )
arrayEmbeddedProperty.any()
arrayEmbeddedProperty.count( longProperty > 100 ) = 1
arrayEmbeddedProperty.count() > 5 and < 10
credentials.count() > 1 (on sources)
credentials.some(name like "correct")
credentials.none(name is not "correct")
credentials.all(name is not "incorrect")
credentials.any($fulltext like "a")
source.credentials.some(username like "admin") (on catalog items)

Back references

You can also define conditions on a set of incoming references by expressing them on the origin entities. This mechanism uses the same aggregation operators as the previously described array embedded properties.

To do this, specify the referencing entity and the referencing property (both are optional):

@ referencingNode ( referencingProperty ) - The referencingProperty defined in referencingNode points to the current entity.
@ referencingNode () - Any reference property defined in the referencing entity points to the current entity.
@ ( referencingProperty ) - The referencingProperty defined in any entity points to the current entity.
@ () - Any property defined in any entity points to the current entity.

Although optional, we recommend always specifying the referencingNode and its referencingProperty to avoid unexpected behavior.

Currently, different properties of different nodes are evaluated separately due to a known issue.

Example 9. Examples

@source(tag).any() - Checks that the current entity is referenced at least once by a source entity via the tag property.
@source(tag) { count(valid IS TRUE) > 2 or any(master IS TRUE and priority > 9000 ) } - Checks that the current entity is referenced by a source entity via the tag property at least twice such that it is valid, or at least once such that it is master and its priority is greater than 9000.
@catalogItem (source).count() == 0 - Not referenced by catalog items. Used on sources.
@catalogItem (source).count({name = "party"}) > 0 - Referenced at least once with a catalog item named "party". Used on sources.

AQL timestamp conditions

All standard operators are supported:

<property> is null
<property> is not null
<property> in (<value1>, <value2>, …)
<property> not in (<value1>, <value2>, …)
<property> = <value> , <property> != <value>
<property> < <value>, <property> > <value>
<property> ⇐ <value>, <property> >= <value>

The value on the right side can be provided in one of two possible formats. The value is always expressed as a string.

Absolute value

The absolute value is a generalized ISO-6801 format:

(YYYY-MM-DD)? # Optional date part
[T ]? # Optional date time separator
(hh:mm(:ss)?(.sss)?)? # Optional time part
(Z|[+-]h(h(:?mm(:?ss)?)?)?)? # Optional time zone part

The value is then parses in the following order:

OffsetDateTime - All parts are mandatory.
LocalDateTime - Date and time. The server time zone is applied.
OffsetTime - Time and time zone. The current date in the provided time zone is used.
LocalTime - Time. The current date in the server time zone is used.
LocalDate - Date. The beginning of the day in the server time zone is used.

We strongly recommend specifying all parts unless when used for ad-hoc queries.

Relative value

The timestamp starts with now representing the current timestamp. The format uses the following units: second, minute, hour, day, week, month, year.

The current timestamp can be divided by a unit and shifted by a multiple of a unit:

---
now
(TZ)? (/ UNIT)?
([+-] AMOUNT UNITs?)?
---

The division truncates the timestamp to the beginning of the particular unit. It can be used to find everything created since midnight of the current day, or in the current month, in comparison to finding everything created in the last 24 hours or the last 30 to 31 days.

You need to provide the time zone for units day, week, month, year so that the time when the day starts can be derived. If the time zone is not provided, the server time zone is used. The format of the time zone is the same as for the absolute value and the beginning of a week is always Monday.

The addition or subtraction is applied after the division (if the division was specified). The amount is an integer and the unit can be in plural.

Example use cases and queries

Find all catalog items that come from particular schema

Query: schema is '<schema_name>' or $parent.originPath is '<schema_name>'

Find all attributes by their name or assigned term

Query:

attributes.any(name is '<attribute_name>')
termInstances.any(displayName is '<term_name>') for catalog item terms and attributes.any(termInstances.any(displayName is '<attribute_name>'))

Find all tables without owner/DQ/profiles (that is, tables that are not managed yet)

Query: dqEvalAggr is null; profiles.count() = 0

Find all attributes without term assigned

Query: attributes.any(termInstances.count() == 0)

Find all tables with data quality under 80%

Query: dqEvalAggr.validityAggr.overallQuality < 80

Find all tables with more than one million records

Query: numberOfRecords > 1000000 or profiles.any(totalNumOfRecords > 1000000)

The second option also returns tables that had a million records at least once during the profiling.

Reference to an AQL timestamp filter with absolute values

Example:

timestamp < '2022-06-05T17:15:15+02:00' # As denoted
timestamp > '2022-01-01' # Midnight in the server time zone
timestamp = '11:35Z' # Today at 11:35 in UTC

Relative value AQL timestamp queries

Example:

now   # simply now
now +2 / day + 8 hours # 8 am today in Prague, Czech Republic
now - 24 hours # 24 hours ago
now / year # Midnight of January 1st in the server time zone

Was this page useful?