Skip to content

Working with Structs

A struct is a collection of zero or more key, value pairs. Keys must be VARCHAR, values can be different types.



STRUCT(json string)



Values within structs can be accessed by key using subscript notation, putting the key in square brackets following the struct.


SELECT birth_place['town']
  FROM $astronauts;

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

struct -> key

struct ->> key

Values within structs can be accessed by key using Accessor notation, support is limited.


`SEARCH(struct, value)`

All values in a struct can be searched for a given value using the SEARCH function.


SELECT name,
       SEARCH(birth_place, 'Italy')
  FROM $astronauts;


Structs have the following limitations

  • Statements cannot ORDER BY a struct column
  • Statements cannot contain DISTINCT and JOIN 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


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.