Struct
Conceptually, a STRUCT column contains an ordered list of columns called “entries”. The entries are referenced by name using strings. This document refers to those entry names as keys. Each row in the STRUCT column must have the same keys. The names of the struct entries are part of the schema. Each row in a STRUCT column must have the same layout. The names of the struct entries are case-insensitive.
STRUCTs are typically used to nest multiple columns into a single column, and the nested column can be of any type, including other STRUCTs and LISTs.
STRUCTs are similar to PostgreSQL's ROW type. The key difference is that SereneDB STRUCTs require the same keys in each row of a STRUCT column. This allows SereneDB to provide significantly improved performance by fully utilizing its vectorized execution engine, and also enforces type consistency for improved correctness. SereneDB's row function returns a TUPLE, a struct without field names, which casts to a STRUCT field by field, and ROW(a INTEGER, b VARCHAR) as a type is another way to write STRUCT(a INTEGER, b VARCHAR). See an example below and the STRUCT functions documentation for details.
See the data types overview for a comparison between nested data types.
Creating Structs
Structs can be created using the struct_pack(name := expr, ...) function, the equivalent array notation {'name': expr, ...}, using a row variable, or by casting the tuple that the row function returns.
Create a struct using the struct_pack function. Note the lack of single quotes around the keys and the use of the := operator:
SELECT struct_pack(key1 := 'value1', key2 := 42) AS s;s ------------- (value1,42)
Create a struct using the array notation:
SELECT {'key1': 'value1', 'key2': 42} AS s;s ------------- (value1,42)
Create a struct using a row variable:
SELECT d AS s FROM (SELECT 'value1' AS key1, 42 AS key2) d;s ------------- (value1,42)
Create a struct of integers:
SELECT {'x': 1, 'y': 2, 'z': 3} AS s;s --------- (1,2,3)
Create a struct of strings with a NULL value:
SELECT {'yes': 'alpha', 'maybe': 'beta', 'huh': NULL, 'no': 'gamma'} AS s;s --------------------- (alpha,beta,,gamma)
Create a struct with a different type for each key:
SELECT {'key1': 'string', 'key2': 1, 'key3': 12.345} AS s;s ------------------- (string,1,12.345)
Create a struct of structs with NULL values:
SELECT { 'birds': {'yes': 'duck', 'maybe': 'goose', 'huh': NULL, 'no': 'heron'}, 'aliens': NULL, 'amphibians': {'yes': 'frog', 'maybe': 'salamander', 'huh': 'dragon', 'no': 'toad'} } AS s; s
----------------------------------------------------------
("(duck,goose,,heron)",,"(frog,salamander,dragon,toad)")Adding or Updating Fields of Structs
To add new fields or update existing ones, you can use struct_update:
SELECT struct_update({'a': 1, 'b': 2}, b := 3, c := 4) AS s;s --------- (1,3,4)
Alternatively, struct_insert also allows adding new fields but not updating existing ones.
Retrieving from Structs
Retrieving a value from a struct can be accomplished using dot notation, bracket notation, or through struct functions like struct_extract.
Use dot notation to retrieve the value at a key's location. In the following query, the subquery generates a struct column a, which we then query with a.x.
SELECT a.x FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS a);x --- 1
If a key contains a space, simply wrap it in double quotes (").
SELECT a."x space" FROM (SELECT {'x space': 1, 'y': 2, 'z': 3} AS a); x space
---------
1Bracket notation may also be used. Note that this uses single quotes (') since the goal is to specify a certain string key and only constant expressions may be used inside the brackets (no expressions):
SELECT a['x space'] FROM (SELECT {'x space': 1, 'y': 2, 'z': 3} AS a);a --- 1
The struct_extract function is also equivalent. This returns 1:
SELECT struct_extract({'x space': 1, 'y': 2, 'z': 3}, 'x space'); struct_extract
----------------
1unnest / STRUCT.*
Rather than retrieving a single key from a struct, the unnest special function can be used to retrieve all keys from a struct as separate columns.
This is particularly useful when a prior operation creates a struct of unknown shape, or if a query must handle any potential struct keys:
SELECT unnest(a)FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS a);x | y | z ---+---+--- 1 | 2 | 3
The same can be achieved with the star notation (*), which additionally allows modifications of the returned columns:
SELECT a.* EXCLUDE ('y')FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS a);x | z ---+--- 1 | 3
Dot Notation Order of Operations
Referring to structs with dot notation can be ambiguous with referring to schemas and tables. In general, SereneDB looks for columns first, then for struct keys within columns. SereneDB resolves references in these orders, using the first match to occur:
No Dots
SELECT part1FROM tbl; part1
----------
("(42)")part1is a column
One Dot
SELECT part1.part2FROM tbl;part2 ------- (42)
part1is a table,part2is a columnpart1is a column,part2is a property of that column
Two (or More) Dots
SELECT part1.part2.part3FROM tbl; part3
-------
42part1is a schema,part2is a table,part3is a columnpart1is a table,part2is a column,part3is a property of that columnpart1is a column,part2is a property of that column,part3is a property of that column
Any extra parts (e.g., .part4.part5, etc.) are always treated as properties
Creating Structs with the row Function
The row function returns a TUPLE: a struct whose fields have no names.
A tuple casts to a struct with the same number of fields, field by field in order, so row can fill a struct column.
For example, inserting values into a struct column using the row function:
CREATE TABLE t1 (s STRUCT(v VARCHAR, i INTEGER));
INSERT INTO t1 VALUES (row('a', 42));The table will contain a single entry:
SELECT * FROM t1;s -------- (a,42)
The following produces the same result as above:
CREATE TABLE t1 AS ( SELECT row('a', 42)::STRUCT(v VARCHAR, i INTEGER));Initializing a column with the row function creates a TUPLE column:
CREATE TABLE t2 AS SELECT row('a') AS r;
SELECT r, typeof(r) AS type FROM t2;r | type -----+---------------- (a) | TUPLE(VARCHAR)
When casting between structs that have field names, the names of at least one field have to match. Therefore, the following query will fail:
SELECT a::STRUCT(y INTEGER) AS bFROM (SELECT {'x': 42} AS a);db error: ERROR: STRUCT to STRUCT cast must have at least one matching member, inputs are (STRUCT(x INTEGER)) and (STRUCT(y INTEGER))
A workaround for this is to use struct_pack instead:
SELECT struct_pack(y := a.x) AS bFROM (SELECT {'x': 42} AS a);b ------ (42)
The row function can combine values of different types. For example:
SELECT row(x, x + 1, y) FROM (SELECT 1 AS x, 'a' AS y) AS s;row --------- (1,2,a)
With two or more values, row can be left out. The following query returns the same result as the previous one:
SELECT (x, x + 1, y) AS s FROM (SELECT 1 AS x, 'a' AS y);s --------- (1,2,a)
Comparison and Ordering
The STRUCT type can be compared using all the comparison operators.
These comparisons can be used in logical expressions
such as WHERE and HAVING clauses and return BOOLEAN values.
Comparisons are done in lexicographical order, with individual entries being compared as usual except that NULL values are treated as larger than all other values.
Specifically:
- If all values of
s1ands2compare equal, thens1ands2compare equal. - else, if
s1.value[i] < s2.value[i] OR s2.value[i] is NULLfor the first indexiwheres1.value[i] != s2.value[i], thens1is less thans2, and vice versa.
Structs of different types are implicitly cast to a struct type with the union of the involved keys, following the rules for combination casting.
The following queries return true:
SELECT {'k1': 0, 'k2': 0} < {'k1': 1, 'k2': 0};?column? ---------- t
SELECT {'k1': 'hello'} < {'k1': 'world'};?column? ---------- t
SELECT {'k1': 0, 'k2': 0} < {'k1': 0, 'k2': NULL};?column? ---------- t
SELECT {'k1': 0} < {'k2': 0};?column? ---------- t
SELECT {'k1': 0, 'k2': 0} < {'k2': 0, 'k3': 0};?column? ---------- t
SELECT {'k1': 1, 'k2': 0} > {'k3': 0, 'k1': 0};?column? ---------- t
SELECT {'k1': [0]} < {'k1': [0, 0]};?column? ---------- t
SELECT {'k1': 0, 'k2': 0} < {'k3': 0, 'k1': 1};?column? ---------- t
The following queries return false:
SELECT {'k1': 1, 'k2': 0} < {'k1': 0, 'k2': 1};?column? ---------- f
SELECT {'k1': 1} > {'k2': 0};?column? ---------- f
SELECT {'k1': 1, 'k2': 0} > {'k2': 0, 'k3': 0};?column? ---------- f
Updating the Schema
With SereneDB it's possible to update the sub-schema of structs
using the ALTER TABLE clause.
Adding, dropping or renaming a struct field rewrites the stored values of the column; added fields are backfilled with NULL. Nested structs are addressed with a dotted path (s.sub.field).
To follow the examples, initialize the test table as follows:
CREATE TABLE test (s STRUCT(i INTEGER, j INTEGER));
INSERT INTO test VALUES (ROW(1, 1)), (ROW(2, 2));Adding a Field
Add field k INTEGER to struct s in table test:
ALTER TABLE test ADD COLUMN s.k INTEGER;
FROM test;s -------- (1,1,) (2,2,)
Dropping a Field
Drop field i from struct s in table test:
ALTER TABLE test DROP COLUMN s.i;
FROM test;s ------ (1,) (2,)
Renaming a Field
Rename field j of struct s to v1 in table test:
ALTER TABLE test RENAME COLUMN s.j TO v1;
FROM test;s ------ (1,) (2,)
Functions
See Struct Functions.