Join functions
Functions that join records from subqueries.
lookup
Returns a record from a subquery (the lookup table) producing a match between a field in the source table (sourceField) and a field in the lookup table (lookupField).
In contrast to the lookup command, the lookup function nests all included fields in the form of a DQL record.
In a case, where more than one match is produced in the lookup table, only the first matching record of the lookup table is returned.
Syntax
lookup(lookupTable, lookupField [, sourceField] [, executionOrder])
Parameters
| Parameter | Type | Description | Required |
|---|---|---|---|
lookupTable | execution block | Subquery producing the lookup table. | required |
sourceField | expression | The matching field in the source table (left side). | optional |
lookupField | expression | The matching field in the lookup table (right side). | required |
executionOrder | Defines which side of the join will be executed first. The allowed values are | optional |
Returns
The data type of the returned value is record.
Examples
Example 1
data record(location = "Vienna", bookings = 1337),record(location = "London", bookings = 2431)| fieldsAdd result = lookup([data record(city = "Vienna", country = "Austria"),record(city = "Barcelona", country = "Spain")],sourceField:location,lookupField:city)
Query result:
| location | bookings | result |
|---|---|---|
|
| city: |
|
| null |
Example 2
In a case, where more than one match is produced in the lookup table, only the first matching record of the lookup table is returned.
data record(location = "Barcelona"),record(location = "London")| fieldsAdd result = lookup([data record(team = "FC Barcelona", city = "Barcelona"),record(team = "Chelsea", city = "London"),record(team = "FC Arsenal", city = "London"),record(team = "Tottenham Hotspur", city = "London")],sourceField:location,lookupField:city)
Query result:
| location | result |
|---|---|
| team: |
| team: |
Limits
The same limits as described for the join command apply.