Nested data and JSON functions

VegaDB can keep nested values nested through SQL, prepared parameters and PostgreSQL client results. Cast JSON to an explicit type when a stable schema is known; keep it as JSON when shape varies by row.

LIST

SELECT list_transform(scores, x -> round(x * 100, 1)) AS percentages
FROM assessments;

Use unnest(list) in a SELECT or lateral relation to expand elements into rows.

Most list functions also have array_* aliases for compatibility, including array_extract, array_slice, array_append, array_prepend, array_contains, array_position, array_distinct, array_sort, array_filter, array_transform, array_reduce, array_where, array_select and array_zip. In those aliases, “array” can refer to a variable-length list; fixed-length ARRAY values are documented separately below.

ARRAY and vector operations

Fixed-length arrays support array_value, element access, casts to/from lists and numeric vector functions: array_distance, array_cosine_distance, array_cosine_similarity, array_inner_product, array_dot_product, array_negative_inner_product, array_negative_dot_product and array_cross_product for compatible dimensions and types.

SELECT document_id,
       array_cosine_similarity(embedding, $1::FLOAT[768]) AS score
FROM documents
ORDER BY score DESC
LIMIT 20;

STRUCT

Create structs with struct_pack, row or a typed cast. Read fields with dot notation, bracket notation, a one-based positional index, struct_extract or struct_extract_at. Use struct_insert, struct_update or struct_concat to produce a changed struct. struct_contains and struct_has test whether a value occurs among the fields.

SELECT struct_pack(id := customer_id, tier := segment) AS customer
FROM customers;

MAP

map, map_from_entries, map_entries, map_keys, map_values, map_extract, map_extract_value, element_at, map_contains, map_contains_entry, map_contains_value, cardinality and map_concat create and query key/value collections. Keys within one map must be unique and non-NULL.

UNION

union_value(tag := value) constructs a tagged union. union_tag reads the active tag and union_extract reads a named member. A cast can add compatible members to an existing union type.

JSON

The -> operator returns JSON and ->> returns text. JSONPath and JSON Pointer paths are supported by the corresponding overloads.

SELECT payload->>'$.customer.id' AS customer_id,
       json_transform(payload, '{"amount":"DECIMAL(18,2)"}') AS typed
FROM raw_events
WHERE json_valid(payload);