Skip to main content
Skip to main content
Edit this page

JSON

InputOutputAlias
✔✔

Description​

The JSON format reads and outputs data in the JSON format.

The JSON format returns the following:

ParameterDescription
metaColumn names and types.
dataData tables
rowsThe total number of output rows.
rows_before_limit_at_leastThe minimal number of rows there would have been without LIMIT. Output only if the query contains LIMIT. If the query contains GROUP BY, rows_before_limit_at_least is the exact number of rows there would have been without a LIMIT.
statisticsStatistics such as elapsed, rows_read, bytes_read.
totalsTotal values (when using WITH TOTALS).
extremesExtreme values (when extremes are set to 1).

The JSON type is compatible with JavaScript. To ensure this, some characters are additionally escaped:

  • the slash / is escaped as \/
  • alternative line breaks U+2028 and U+2029, which break some browsers, are escaped as \uXXXX.
  • ASCII control characters are escaped: backspace, form feed, line feed, carriage return, and horizontal tab are replaced with \b, \f, \n, \r, \t , as well as the remaining bytes in the 00-1F range using \uXXXX sequences.
  • Invalid UTF-8 sequences are changed to the replacement character � so the output text will consist of valid UTF-8 sequences.

For compatibility with JavaScript, Int64 and UInt64 integers are enclosed in double quotes by default. To remove the quotes, you can set the configuration parameter output_format_json_quote_64bit_integers to 0.

ClickHouse supports NULL, which is displayed as null in the JSON output. To enable +nan, -nan, +inf, -inf values in output, set the output_format_json_quote_denormals to 1.

Example Usage​

Example:

Format Settings​

For JSON input format, if setting input_format_json_validate_types_from_metadata is set to 1, the types from metadata in input data will be compared with the types of the corresponding columns from the table.

See Also​