json_each_row ​

Description ​

One named JSON object per row. Missing fields can use defaults; unknown fields error unless ignore_unknown_fields is enabled.

Input and output. Select it with FORMAT json_each_row. Names ignore ASCII case; use the snake_case name shown here.

Every row is a named JSON object. Object keys select destination columns, so their order does not need to match the target column order. Each value is converted to its declared type.

Missing fields use the configured SQL/native defaults. Unknown fields are rejected unless ignore_unknown_fields is enabled. Use aliases to produce stable output keys and a separate later request after inline INSERT data.

SELECT expressions FORMAT json_each_row;
INSERT INTO table [(columns)] [SETTINGS ...] FORMAT json_each_row
<data>

Example usage ​

SELECT CAST(7 AS Int64) AS id, 'Ada' AS name FORMAT json_each_row;

{"id":7,"name":"Ada"}

SQL NULL as JSON null ​

SELECT NULL AS value, 0 AS count
FORMAT json_each_row;

{"value":null,"count":0}

This differs from json_strings_each_row, which writes the textual NULL marker as a JSON string.

Format settings ​

Use a statement SETTINGS clause; these options are not persisted by SET.