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

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:

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:

Limits

The same limits as described for the join command apply.