AQL Query Syntax

Ataccama Query Language (AQL) is the filter syntax used to search for assets in ONE. You write conditions against asset properties — filtering by name, data type, quality score, relationships, and more — and AQL returns the matching results.

AQL queries the metadata model behind the catalog. In this context, assets and asset types are referred to as entities.

When to use AQL

AQL search can be used for all assets in ONE that don’t have the full-text search configured. For assets with full-text search configured, you can still switch to AQL by selecting it in the search bar from the All tab on the asset listing screen.

It’s most useful when you need to:

Find assets that match multiple conditions at once.
Spot gaps in your catalog (items without profiling, attributes without terms, assets without an owner).
Monitor data quality across large sets of assets.

How AQL works

Every listing in AQL can be narrowed down by providing a filter: you identify a property and apply a condition on it. The condition doesn’t specify which entity it applies to; that information comes from the context.

You can think of AQL filters as an SQL WHERE clause. The syntax resembles SQL for simple conditions, but AQL can also place conditions on related entities.

For example, to filter sources whose names contain postgres as a substring:

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

To filter sources whose names contain postgres and are referenced by at least two catalog items that each contain an attribute of data type STRING:

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

To find an entity with a specific ID:

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

Note that this AQL query uses a semi-expression (id). A semi-expression is a pattern into which a result of another ONE expression is inserted at runtime.

In this case, id is evaluated at runtime as the value of the column gid, for example, 813adb4c-7746-49ce-gv46-a00d2454432e. The value of the semi-expression is translated to $id and then used as a filter.

Common use cases

Find all catalog items from a particular schema: schema is '<schema_name>' or $parent.originPath is '<schema_name>'
Find all attributes by name: attributes.any(name is '<attribute_name>')
Find all catalog items or attributes by assigned term: termInstances.any(displayName is '<term_name>') for catalog item terms, attributes.any(termInstances.any(displayName is '<term_name>')) for attribute terms
Find all tables without owner, DQ, or profiles (tables that are not managed yet): validityAggr.overallQuality != NULL; profiles.count() = 0
Find all attributes without a term assigned: attributes.any(termInstances.count() == 0)
Find all tables with data quality under 80%: validityAggr.overallQuality < 80
Find all tables with more than one million records: numberOfRecords > 1000000 or profiles.any(totalNumOfRecords > 1000000)

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

Combining conditions

You can combine multiple conditions using logical operators:

NOT (alias: !): The condition must not be satisfied.
AND (&, &&): Both conditions must be satisfied.

OR (|, ||): At least one condition must be satisfied.

Examples on sources

name like "mysql" and profilingLimitAbsoluteMax > 100
name like "mysql" or profilingLimitAbsoluteMax < 100

Operators are prioritized in the order listed: NOT first, then AND, then OR. You can use parentheses to change this order.

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))

Applied to the following data, the results would be:

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.

Condition types

Nullability

Null means that a value is not present. This is different from an empty string (""), zero (0), or false.

You can test nullability on these property types:

Scalar
Single embedded
Reference

Examples

propertyName IS NULL
propertyName IS NOT NULL

/*For example, on sources*/
name is not null

/*For example, on catalog items, single embedded*/
profilingResult is not null

/*For example, on catalog items, reference*/
source is not null

For other equality and inequality operators, see Binary operators.

Scalar property conditions

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

Currently, expressions on the right side of a condition are not supported.

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 syntax accepts any number. Range validation happens at the scalar type level.

Binary operators

You can compare a scalar property value 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 work with all 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. It searches for a substring and is case-insensitive.

Examples

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

Examples on sources

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

In list operator

To check whether a value is in a list, use the IN operator.

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

Examples

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

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. We recommend using this sparingly as it can be confusing.

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

You can place 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 doesn’t need combining operators, you can use the shorthand syntax:

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

Keep in mind:

Single embedded and reference properties can be tested for nullability. 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 $).

Examples

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

Array property conditions

You can also place conditions on array embedded properties, array referenced properties, and back references. The nested condition is aggregated and the number of matching entities is evaluated.

As with single properties, there are two syntaxes:

arrayPropertyName { aggregation( nestedCondition ) }: Lets you combine multiple aggregation conditions on one array property.
arrayPropertyName.aggregation( nestedCondition ): For a single aggregation.

Aggregation operators

The following operators can be used in aggregations:

all( nestedCondition ): Requires a nested condition. All entities must match.
none( nestedCondition ): Requires a nested condition. No entities must match.
any( nestedCondition ), some( nestedCondition ): The nested condition is optional. At least one entity must match.
count( nestedCondition ) scalarCondition: The nested condition is optional. The number of matching entities is tested with a scalar condition.

Array properties cannot be tested for nullability. If the array is empty (contains no nested entities), all returns true and the match count equals the total count.

Examples

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

Back references

You can define conditions on incoming references by targeting the entities that reference the current one. This uses the same aggregation operators as array embedded properties.

Specify the referencing entity and property using one of the following combinations:

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

Although the entity and property can be omitted, we recommend always specifying both to avoid unexpected behavior.

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

Examples:

@source(tag).any(): The current entity is referenced at least once by a source entity through the tag property.
@source(tag) { count(valid IS TRUE) > 2 or any(master IS TRUE and priority > 9000 ) }: The current entity is referenced by a source entity through tag at least twice where valid is true, or at least once where master is true and priority exceeds 9000.
@catalogItem (source).count() == 0: Not referenced by any catalog items. Used on sources.
@catalogItem (source).count({name = "party"}) > 0: Referenced at least once by 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 formats. The value is always expressed as a string.

Absolute value

The absolute value uses a generalized ISO-8601 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 parsed 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 you are running ad-hoc queries.

Examples

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

The timestamp starts with now, representing the current timestamp. Supported 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?)?
---

Division truncates the timestamp to the start of that unit. This lets you find everything created since midnight today or since the start of the current month, as opposed to the last 24 hours or 30 days.

For day, week, month, and year units, provide a time zone so that the start of the period can be determined. If no time zone is provided, the server time zone is used.

The time zone format is the same as for absolute values. The start of a week is always Monday.

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

Examples

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?