Records

Construction

Description

(1, 2.0)
Constructs a record with two fields named _1 and _ 2.
Field _1 contains int value 1 while field _2 contains float value 2.0.
(a: 1, b: 2.0)
Constructs a record with two fields named a and b.
Field a contains int value 1 while field b contains float value 2.0.

The syntax (a: 1, 2) where some fields named and others are not is not allowed.

It is possible to construct a record with repeated field names as in (a: 1, a: 2, b: 3). However, requesting field a will trigger an ambiguity error since it is not known which a to retrieve. Field b can be requested normally.

Note

Record types have an order of their fields that is maintained. However, similarly to SQL implementations, there is no possibility to access the fields of a record by ordinal position. (Otherwise queries could accidentally refer to the wrong column in case of schema/view changes.)

Projection

Extract a field from a record.

Syntax:

<record>.<idn>

For example:

r := (a: 1, b: 2.0)
r.a // 1
r.b // 2.0

Merge: ++

Merge two records creating a new record with all fields of the first record followed by all fields of the second record.

Syntax:

<record> ++ <record>

For example:

(1, 2) ++ (a: 3, b: 4) // (_1: 1, _2: 2, a: 3, b: 4)
(a: 1, b: 2.0) ++ (a: 2) // (a: 1, b: 2.0, a: 2)

Merge: +++

Create a new record with the elements from another record. Fields present in the first record are updated, while fields missing in the first record are added to it.

Syntax:

<record> +++ <record>

For example:

(a: 1, b: 2.0) +++ (a: "2", c: 3) // (a: "2", b: 2.0, c: 3)

FIELDS

Returns collection of strings with field names of a record.

Syntax:

FIELDS(<record>)

For example:

FIELDS((a: 1, b: "foo")) // ["a", "b"]

INTO

INTO is a convenient shorthand to construct a record from an existing record.

Syntax:

<record> INTO <record>

For example:

("John",40) INTO (name: _1, age: _2) // (name: "John", age: 40)
(a: 1, b: "Foo") INTO (x: b, y: a, z: 3.0) // (x: "Foo", y: 1, z: 3.0)

Note

The INTO is often used to name columns.

Comparisons

Records cannot be compared with equality operators (=, <>, etc). However, records can be compared using IS DISTINCT FROM / IS NOT DISTINCT FROM as described in Comparisons.

Casts

Records can be casted to other records with the same field names and whose field values can be casted to the requested type.

For example:

cast( (a: 1, b: 2) as record(a: float, b: string) ) // (a: 1.0, b: "2")
cast( (a: 1, b: 2) as record(a: float) ) // Error!

A collection of tuple-2 can be casted to a map. If the element corresponding to the key appears more than once in the collection, only one of the keys is kept without ensuring which one.

For example:

CAST([(1,"x"), (2, "y")] AS MAP(int, string)) // map(1: "x", 2: "y")