Skip to content
Coordinator DB
View as Markdown
View as Markdown

json_strings_each_row ​

Description ​

One named JSON object per row with every field encoded as a JSON string. SQL NULL is the string "ᴺᵁᴸᴸ"; collections use their complete native text inside the string. Known input fields must be strings; bare JSON null is rejected. Nullable targets also accept the string "NULL", ignoring ASCII case. Input converts the complete field text through the declared column type.

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

Objects retain named fields, but every value is a JSON string containing complete native text. Numeric digits do not pass through a JSON numeric representation, and collections remain text inside their field string.

SQL NULL uses the quoted marker shown above. Input known fields must be strings; their complete text is converted through each destination declaration. Use json_each_row when the consumer needs ordinary JSON value types.

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

Example usage ​

sql
SELECT CAST(7 AS Int64) AS id, 'Ada' AS name FORMAT json_strings_each_row;
text
{"id":"7","name":"Ada"}
sql
SELECT NULL AS value FORMAT json_strings_each_row;
text
{"value":"ᴺᵁᴸᴸ"}

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_array_of_rows Bool 0 Wrap all output rows in one JSON array.
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_strict_escapes Bool 1 Reject invalid JSON escapes; otherwise preserve their literal backslash spelling.
json_strings_each_row_sql_defaults Bool 1 Evaluate SQL DEFAULT expressions for omitted or defaulted whole-text JSON fields; off supplies their native type defaults.
json_validate_utf8 Bool 0 Replace invalid UTF-8 sequences in whole-text JSON output with replacement characters; off preserves byte-string payloads.
snappy_mode String basic Read Snappy client files as basic (Hadoop blocks) or framed (standard streaming format).