Skip to content
Coordinator DB
View as Markdown
View as Markdown

json_object_each_row ​

Description ​

One JSON object whose outer keys identify named row objects. json_object_each_row_name_column can expose the key as a column.

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

The document's outer keys identify rows; each associated object holds that row's fields. The configured name column can expose the outer key as an ordinary field.

This layout is distinct from json_each_row, whose top-level objects are already rows. Name-column collisions and field conversions follow the selected schema and settings.

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

Example usage ​

sql
SELECT CAST(7 AS Int64) AS id, 'Ada' AS name FORMAT json_object_each_row;
text
{
	"row_1": {"id":7,"name":"Ada"}
}

Format settings ​

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

Setting Type Default Meaning
ignore_unknown_fields Bool 0 Skip input keys and header names that match no target column instead of failing.
json_arrays_as_strings Bool 1 Accept JSON arrays in string input columns and retain their JSON text.
json_bools_as_numbers Bool 1 Accept JSON Boolean input in numeric columns as zero or one.
json_bools_as_strings Bool 1 Accept JSON Boolean input in string columns as true or false text.
json_empty_as_default Bool 0 Give empty JSON string fields their SQL or native type default.
json_escape_slashes Bool 1 Escape unescaped forward slashes in JSON output strings.
json_named_tuples_as_objects Bool 1 Read and write named Tuple values as objects; otherwise use positional arrays.
json_numbers_as_strings Bool 1 Accept JSON numbers in string input columns and retain their exact spelling.
json_object_each_row_name_column String '' Use this string column for outer row keys and omit it from row objects; empty generates row_1, row_2, and so on.
json_object_each_row_sql_defaults Bool 1 Evaluate SQL DEFAULT expressions for omitted or defaulted row fields; off supplies only their native type defaults.
json_objects_as_strings Bool 1 Accept JSON objects in string input columns and retain their JSON text.
json_quote_decimals Bool 0 Write fixed-point decimal output values as JSON strings.
json_quote_large_floats Bool 0 Write finite Float64 output values as JSON strings.
json_quote_large_integers Bool 0 Write integers of at least 64 bits as JSON strings; retained narrow types stay numeric.
json_quote_special_floats Bool 0 Write non-finite floats as inf, -inf, nan, or -nan strings instead of JSON null.
json_strict_escapes Bool 1 Reject invalid JSON escapes; otherwise preserve their literal backslash spelling.
json_tuple_ignore_unknown_fields Bool 0 Validate then ignore object members outside a named Tuple declaration.
json_tuple_missing_as_default Bool 1 Give missing named Tuple members their declared type defaults.
snappy_mode String basic Read Snappy client files as basic (Hadoop blocks) or framed (standard streaming format).