Expand JSON into rows ​

Use a table function to query a JSON document as rows. The resulting relation can be filtered, joined or aggregated with ordinary SQL.

Immediate and recursive members ​

json_each expands immediate children. json_tree traverses recursively. JSON array keys are zero-based, unlike native Array element positions.

SELECT key, value
FROM json_each('[10,20]')
ORDER BY key
FORMAT csv;

0,10
1,20

Typed records ​

json_to_record projects an object into an explicit typed schema:

SELECT *
FROM json_to_record('{"id":7,"name":"Ada"}')
     AS record(id UInt64, name String)
FORMAT csv;

7,"Ada"

The alias schema defines the columns and conversions. This does not infer a persistent table definition. Use the appropriate recordset function for a sequence of records.

See JSON functions for schemas, missing fields, JSONPath and other expansion functions.