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]) -
Parameters
Name Type Mandatory Default Constraints Description lookupTable
execution block
yes
Subquery producing the lookup table.
sourceField
expression
no
The matching field in the source table (left side).
lookupField
expression
yes
The matching field in the lookup table (right side).
-
Example 1
1data record(location = "Vienna", bookings = 1337),2 record(location = "London", bookings = 2431)3| fieldsAdd result = lookup([4 data record(city = "Vienna", country = "Austria"),5 record(city = "Barcelona", country = "Spain")6 ],7 sourceField:location,8 lookupField:city)Query result
location bookings result Vienna1,337city:
Vienna
country:AustriaLondon2,431null
-
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.
1data record(location = "Barcelona"),2 record(location = "London")3| fieldsAdd result = lookup([4 data record(team = "FC Barcelona", city = "Barcelona"),5 record(team = "Chelsea", city = "London"),6 record(team = "FC Arsenal", city = "London"),7 record(team = "Tottenham Hotspur", city = "London")8 ],9 sourceField:location,10 lookupField:city)Query result
location result Barcelonateam:
FC Barcelona
city:BarcelonaLondonteam:
Chelsea
city:London