Semi-structured data types

Use array, object, and any for values whose nested structure or element types vary.

Type	Contents	Example
`array`	Ordered elements of any supported type, including NULL.	`['cart_viewed', 'payment_failed']`
`object`	String keys and values of any supported type, including NULL.	`{'channel': 'web', 'total_cents': 7900}`
`any`	A value of any supported type. Different rows may contain different types.	`parse_json('{"channel":"web"}')`

Inserting semi-structured data

Store stable event fields in typed columns and varying attributes in an object column. Create a table for checkout events:

CREATE TABLE reference_checkout_payloads (event_id string, payload object)

Insert two checkout events:

VALUES
    ('evt-17', parse_json('{"channel":"web","total_cents":7900}')),
    ('evt-42', {'channel': 'mobile', 'total_cents': 4900, 'coupon': 'WELCOME'}::any)
SELECT $0 AS event_id, $1::object AS payload
INSERT INTO reference_checkout_payloads

Read their shared and optional fields:

FROM reference_checkout_payloads
SELECT event_id, payload['channel']::string AS channel, payload['coupon']::string AS coupon
ORDER BY event_id

The final SELECT returns:

+----------+---------+---------+
| event_id | channel | coupon  |
+----------+---------+---------+
| evt-17   | web     | NULL    |
| evt-42   | mobile  | WELCOME |
+----------+---------+---------+

The two payloads have different keys. Reading the missing coupon field returns NULL.

Array and object literals can contain timestamps, intervals, binary values, and nested arrays or objects as well as JSON-compatible values. Cast strings explicitly when they represent a timestamp or interval.

Using semi-structured values

Element access with [] returns any. Array indices start at 0; negative indices count from the end. Object keys are strings. Missing elements and keys return NULL.

SELECT
    ['cart_viewed', 'checkout_started', 'payment_completed'][-1] AS last_step,
    {'order': {'id': 'o-17'}}['order']['id'] AS order_id,
    {'channel': 'web'}['coupon'] AS coupon

+-------------------+----------+--------+
| last_step         | order_id | coupon |
+-------------------+----------+--------+
| payment_completed | o-17     | NULL   |
+-------------------+----------+--------+

Cast an any value to the type required by the next function. Use TRY(...) to return NULL for incompatible values. Here length needs an array, but one event contains an object:

VALUES
    ('evt-17', '["sku-17","sku-42"]'),
    ('evt-42', '{"sku":"sku-63"}'),
    ('evt-63', 'null')
SELECT $0 AS event_id, parse_json($1) AS items
SELECT event_id, typeof(items) AS received_type, TRY(length(items::array)) AS item_count

+----------+---------------+------------+
| event_id | received_type | item_count |
+----------+---------------+------------+
| evt-17   | array         | 2          |
| evt-42   | object        | NULL       |
| evt-63   | null          | NULL       |
+----------+---------------+------------+

typeof(<any>) reports the contained type as a string. typeof(<array>) instead returns the distinct element types. Cast an array to any when you want its own type name. Here one item ID has an unexpected numeric type:

SELECT
    typeof(['sku-17', 'sku-42', 63]) AS element_types,
    typeof(['sku-17', 'sku-42', 63]::any) AS value_type

+------------------+------------+
| element_types    | value_type |
+------------------+------------+
| ["int","string"] | array      |
+------------------+------------+

NULL and JSON

JSON null and ScopeQL NULL both become NULL values. Arrays retain NULL elements, and objects retain keys with NULL values.

to_json serializes these values as JSON text. A NULL input produces the string 'null'. Casting text to any does not parse it; use parse_json instead.

Comparison

Arrays and objects use lexicographic comparison. any values have a defined order that can be used in ORDER BY. Different contained types compare by the following order, from greatest to least:

object > array > interval > timestamp > boolean > string > binary > float > uint > int

Types remain distinct: an int value of 1 and a float value of 1.0 are different array elements. This affects functions such as contains, dedup, and exclude.

Ordinary comparisons return NULL if either operand is NULL. Use IS for NULL-safe equality and NULLS FIRST or NULLS LAST to control NULL placement in ORDER BY.