Working with Structs
A struct is a collection of zero or more key, value pairs. Keys must be VARCHAR
, values can be different types.
Actions
Construct
Reading
Values within structs can be accessed by key using subscript notation, putting the key in square brackets following the struct.
Example:
Struct values can be treated the same as other identifiers and, for example, used within SELECT
, WHERE
and GROUP BY
clauses:
SELECT birth_place['town'], COUNT(*)
FROM $astronauts
WHERE birth_place['state'] IS NOT NULL
GROUP BY birth_place['town'];
Beta Functionality
Values within structs can be accessed by key using Accessor notation, support is limited.
Searching
All values in a struct can be searched for a given value using the SEARCH
function.
Example:
Limitations
Structs have the following limitations
- Statements cannot
ORDER BY
a struct column - Statements cannot contain
DISTINCT
andJOIN
when the relations include struct columns - Structs cannot be used in comparisons, however, their component values can be
- Subscript references (the bit in square brackets) must be in single quotes only
Note
Some restrictions may be resolved by the query optimizer, for example, Projection Pushdown may remove struct columns as part of optimization. However, you should not rely on the optimizer to take any particular action.