Struct

​

Define a struct type

Syntax:STRUCT< >

Struct types are declared using the angle brackets (< and >).

​

Examples

The statement below creates a table x that contains struct a, which contains two nested columns (b and c) that are both integers.

CREATE TABLE x (a STRUCT <b INTEGER, c INTEGER>, d INTEGER);

The statement below creates a table y that contains struct a, which contains another struct c.

CREATE TABLE y (a STRUCT <b STRUCT<c INTEGER>, d INTEGER>, e VARCHAR);

Below is a real world example.

CREATE TABLE trip (
        id VARCHAR,
        started_at TIMESTAMP,
        completed_at TIMESTAMP,
        distance DOUBLE PRECISION,
        fare STRUCT <
            initial_charge DOUBLE PRECISION,
            subsequent_charge DOUBLE PRECISION,
            surcharge DOUBLE PRECISION,
            tolls DOUBLE PRECISION
            >
        );

You can also use the struct type to parse a string that contains data in JSON format into its proper format.

For instance, if your data is structured like so:

{
  "nested": "{\"v1\": 10, \"v2\": \"hello\"}"
}

And you want it to be structured like so:

{
  "nested": {
    "v1": 10,
    "v2": "hello"
  }
}

You can parse it using the struct type.

CREATE SOURCE z (nested STRUCT <v1 INTEGER, v2 VARCHAR>) FORMAT JSON;

​

Add values to a struct

To add values to structs, enclose the nested data with () in the SQL statement. For example, (1, true). Alternatively, you can also use ROW(1, true).

​

Examples

The statement below adds values to table x.

INSERT INTO x VALUES (ROW(3, 4), 5);

The statement below adds values to table y.

INSERT INTO y VALUES (ROW(ROW(6), 7), 8);

The statement below adds values to table trip.

INSERT INTO trip VALUES
        (
            '1234ABCD',
            '2022-07-28 11:04:05',
            '2022-07-28 11:15:22',
            6.1,
            ROW(1.0, 4.0, 1.5, 2.0)
        );

​

Retrieve data in a struct

To retrieve data in a struct, enclose the struct name with () and use the dot operator to specify the nested column. For example, to access the initial_charge column under fare in the trip schema, use (fare).initial_charge.

​

Examples

SELECT (a).b, d
FROM x;

SELECT ((a).b).c, e
FROM y;

SELECT id, (fare).initial_charge
FROM trip;

​

Casting

Structs can be casted explicitly or implicitly to structs if the nested expressions and types can be casted.

​

Examples

SELECT (1, (2, 3))::STRUCT<i BIGINT, j STRUCT<a BIGINT, b VARCHAR>>;
-----Result
(1,(2,3))

SELECT ROW(1, ROW('1', 1)) = ROW('1', ROW(1, '1'));
-----
t