Skip to main content

​
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