JSON format settings
JSON settings control field conversion, quoting, header matching and document layout. Use a SETTINGS clause on the statement; the next statement starts from defaults again.
A setting applies only to the JSON layouts that support it. Whole-object json_as_object and json_as_string input does not use the ordinary row JSON options. The individual format pages list their applicable subset.
The ordinary json_each_row codec always writes numeric values as JSON numbers; the json_quote options do not change it. Choose a supported compact/object layout when quoted numeric output is required.
Use a statement SETTINGS clause. Defaults in system.settings are read-only;
format settings cannot be persisted with SET. Put input settings before
FORMAT, because the bytes after the format name are input data.
json_array_of_rows
Type: Bool
Default value: 0
Wrap all output rows in one JSON array.
Applies to: json_compact_each_row, json_compact_each_row_with_names_and_types, json_compact_strings_each_row, json_strings_each_row.
json_arrays_as_strings
Type: Bool
Default value: 1
Accept JSON arrays in string input columns and retain their JSON text.
Applies to: json_compact_each_row, json_compact_each_row_with_names_and_types, json_object_each_row.
json_bools_as_numbers
Type: Bool
Default value: 1
Accept JSON Boolean input in numeric columns as zero or one.
Applies to: json_compact_each_row, json_compact_each_row_with_names_and_types, json_object_each_row.
json_bools_as_strings
Type: Bool
Default value: 1
Accept JSON Boolean input in string columns as true or false text.
Applies to: json_compact_each_row, json_compact_each_row_with_names_and_types, json_object_each_row.
json_check_types
Type: Bool
Default value: 1
Compare input metadata or header types with the bound target column types.
Applies to: json, json_compact_each_row_with_names_and_types.
json_columns_sql_defaults
Type: Bool
Default value: 1
Evaluate SQL DEFAULT expressions for omitted input columns; off supplies only their native type defaults.
Applies to: json_columns.
json_compact_columns_sql_defaults
Type: Bool
Default value: 1
Evaluate SQL DEFAULT expressions for omitted trailing input columns; off supplies only their native type defaults.
Applies to: json_compact_columns.
json_compact_strings_each_row_sql_defaults
Type: Bool
Default value: 1
Evaluate SQL DEFAULT expressions for missing or defaulted positional whole-text JSON fields; off supplies their native type defaults.
Applies to: json_compact_strings_each_row.
json_compact_uneven_rows
Type: Bool
Default value: 0
Give missing positional fields their SQL or native defaults and validate then skip extra fields.
Applies to: json_compact_each_row, json_compact_each_row_with_names_and_types, json_compact_strings_each_row.
json_empty_as_default
Type: Bool
Default value: 0
Give empty JSON string fields their SQL or native type default.
Applies to: json_compact_each_row, json_compact_each_row_with_names_and_types, json_compact_strings_each_row, json_object_each_row, json_strings_each_row.
json_escape_slashes
Type: Bool
Default value: 1
Escape unescaped forward slashes in JSON output strings.
Applies to: json_compact_each_row, json_compact_each_row_with_names_and_types, json_compact_strings_each_row, json_object_each_row, json_strings_each_row.
json_match_names
Type: Bool
Default value: 1
Match input header names to target columns ignoring ASCII case; when off, skip the names header and read in target order.
Applies to: json_compact_each_row_with_names_and_types.
json_named_tuples_as_objects
Type: Bool
Default value: 1
Read and write named Tuple values as objects; otherwise use positional arrays.
Applies to: json_compact_each_row, json_compact_each_row_with_names_and_types, json_object_each_row.
json_numbers_as_strings
Type: Bool
Default value: 1
Accept JSON numbers in string input columns and retain their exact spelling.
Applies to: json_compact_each_row, json_compact_each_row_with_names_and_types, json_object_each_row.
json_object_each_row_name_column
Type: String
Default value: ''
Use this string column for outer row keys and omit it from row objects; empty generates row_1, row_2, and so on.
Applies to: json_object_each_row.
json_object_each_row_sql_defaults
Type: Bool
Default value: 1
Evaluate SQL DEFAULT expressions for omitted or defaulted row fields; off supplies only their native type defaults.
Applies to: json_object_each_row.
json_objects_as_strings
Type: Bool
Default value: 1
Accept JSON objects in string input columns and retain their JSON text.
Applies to: json_compact_each_row, json_compact_each_row_with_names_and_types, json_object_each_row.
json_quote_decimals
Type: Bool
Default value: 0
Write fixed-point decimal output values as JSON strings.
Applies to: json_compact_each_row, json_compact_each_row_with_names_and_types, json_object_each_row.
json_quote_large_floats
Type: Bool
Default value: 0
Write finite Float64 output values as JSON strings.
Applies to: json_compact_each_row, json_compact_each_row_with_names_and_types, json_object_each_row.
json_quote_large_integers
Type: Bool
Default value: 0
Write integers of at least 64 bits as JSON strings; retained narrow types stay numeric.
Applies to: json, json_compact_each_row, json_compact_each_row_with_names_and_types, json_object_each_row.
json_quote_special_floats
Type: Bool
Default value: 0
Write non-finite floats as inf, -inf, nan, or -nan strings instead of JSON null.
Applies to: json, json_compact_each_row, json_compact_each_row_with_names_and_types, json_object_each_row.
json_strict_escapes
Type: Bool
Default value: 1
Reject invalid JSON escapes; otherwise preserve their literal backslash spelling.
Applies to: json_compact_each_row, json_compact_each_row_with_names_and_types, json_compact_strings_each_row, json_object_each_row, json_strings_each_row.
json_strings_each_row_sql_defaults
Type: Bool
Default value: 1
Evaluate SQL DEFAULT expressions for omitted or defaulted whole-text JSON fields; off supplies their native type defaults.
Applies to: json_strings_each_row.
json_tuple_ignore_unknown_fields
Type: Bool
Default value: 0
Validate then ignore object members outside a named Tuple declaration.
Applies to: json_compact_each_row, json_compact_each_row_with_names_and_types, json_object_each_row.
json_tuple_missing_as_default
Type: Bool
Default value: 1
Give missing named Tuple members their declared type defaults.
Applies to: json_compact_each_row, json_compact_each_row_with_names_and_types, json_object_each_row.
json_validate_utf8
Type: Bool
Default value: 0
Replace invalid UTF-8 sequences in whole-text JSON output with replacement characters; off preserves byte-string payloads.
Applies to: json_compact_strings_each_row, json_strings_each_row.