JSONObjectEachRow

输入	输出	别名
✔	✔

说明

在这种格式中，所有数据都表示为一个 JSON 对象，其中每一行都对应该对象中的一个独立字段，类似于 JSONEachRow 格式。

示例用法

基础示例

给定如下 JSON：

{
  "row_1": {"num": 42, "str": "hello", "arr":  [0,1]},
  "row_2": {"num": 43, "str": "hello", "arr":  [0,1,2]},
  "row_3": {"num": 44, "str": "hello", "arr":  [0,1,2,3]}
}

如果要将对象名称用作列值，可以使用特殊设置 format_json_object_each_row_column_for_object_name。该设置的值应设为某个列名，该列名会在结果对象中用作某一行的 JSON 键。

输出

假设我们有一个名为 test 的表，包含两列：

┌─object_name─┬─number─┐
│ first_obj   │      1 │
│ second_obj  │      2 │
│ third_obj   │      3 │
└─────────────┴────────┘

下面以 JSONObjectEachRow 格式输出，并使用 format_json_object_each_row_column_for_object_name 设置：

Query

SELECT * FROM test SETTINGS format_json_object_each_row_column_for_object_name='object_name'

Response

{
    "first_obj": {"number": 1},
    "second_obj": {"number": 2},
    "third_obj": {"number": 3}
}

输入

假设我们将上一个示例的输出保存到了名为 data.json 的文件中：

Query

SELECT * FROM file('data.json', JSONObjectEachRow, 'object_name String, number UInt64') SETTINGS format_json_object_each_row_column_for_object_name='object_name'

Response

┌─object_name─┬─number─┐
│ first_obj   │      1 │
│ second_obj  │      2 │
│ third_obj   │      3 │
└─────────────┴────────┘

这也适用于 schema inference：

Query

DESCRIBE file('data.json', JSONObjectEachRow) SETTING format_json_object_each_row_column_for_object_name='object_name'

Response

┌─name────────┬─type────────────┐
│ object_name │ String          │
│ number      │ Nullable(Int64) │
└─────────────┴─────────────────┘

插入数据

Query

INSERT INTO UserActivity FORMAT JSONEachRow {"PageViews":5, "UserID":"4324182021466249494", "Duration":146,"Sign":-1} {"UserID":"4324182021466249494","PageViews":6,"Duration":185,"Sign":1}

ClickHouse 允许：

对象中的键值对按任意顺序排列。
省略某些值。

ClickHouse 会忽略元素之间的空格以及对象后面的逗号。你可以将所有对象写在同一行中传入，无需用换行符将它们分隔开。

省略值的处理

ClickHouse 会用相应数据类型的默认值来填补被省略的值。如果指定了 DEFAULT expr，ClickHouse 会根据 input_format_defaults_for_omitted_fields 设置采用不同的填补规则。请看下表：

Query

CREATE TABLE IF NOT EXISTS example_table
(
    x UInt32,
    a DEFAULT x * 2
) ENGINE = Memory;

如果 input_format_defaults_for_omitted_fields = 0，则 x 和 a 的默认值均为 0 (即 UInt32 数据类型的默认值) 。
如果 input_format_defaults_for_omitted_fields = 1，则 x 的默认值为 0，但 a 的默认值为 x * 2。

当以 input_format_defaults_for_omitted_fields = 1 插入数据时，相比 input_format_defaults_for_omitted_fields = 0，ClickHouse 会消耗更多计算资源。

查询数据

以 UserActivity 表为例：

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

查询 SELECT * FROM UserActivity FORMAT JSONEachRow 的返回结果为：

{"UserID":"4324182021466249494","PageViews":5,"Duration":146,"Sign":-1}
{"UserID":"4324182021466249494","PageViews":6,"Duration":185,"Sign":1}

与 JSON 格式不同，这里不会替换无效的 UTF-8 序列。值的转义方式与 JSON 相同。

字符串中可以输出任意字节序列。如果你确定表中的数据可以在不丢失任何信息的情况下格式化为 JSON，请使用 JSONEachRow 格式。

Nested 结构的用法

如果你的表中包含 Nested 数据类型的列，则可以插入具有相同结构的 JSON 数据。可通过 input_format_import_nested_json 设置启用此功能。例如，考虑如下表：

Query

CREATE TABLE json_each_row_nested (n Nested (s String, i Int32) ) ENGINE = Memory

正如 Nested 数据类型说明中所示，ClickHouse 会将嵌套结构的每个组成部分视为单独的列 (在我们的表中即 n.s 和 n.i) 。你可以按如下方式插入数据：

Query

INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n.s": ["abc", "def"], "n.i": [1, 23]}

要将数据作为层级化 JSON 对象插入，请设置 input_format_import_nested_json=1。

{
    "n": {
        "s": ["abc", "def"],
        "i": [1, 23]
    }
}

如果未启用此设置，ClickHouse 会抛出异常。

Query

SELECT name, value FROM system.settings WHERE name = 'input_format_import_nested_json'

Response

┌─name────────────────────────────┬─value─┐
│ input_format_import_nested_json │ 0     │
└─────────────────────────────────┴───────┘

Query

INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n": {"s": ["abc", "def"], "i": [1, 23]}}

Response

Code: 117. DB::Exception: Unknown field found while parsing JSONEachRow format: n: (at row 1)

Query

SET input_format_import_nested_json=1
INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n": {"s": ["abc", "def"], "i": [1, 23]}}
SELECT * FROM json_each_row_nested

Response

┌─n.s───────────┬─n.i────┐
│ ['abc','def'] │ [1,23] │
└───────────────┴────────┘

格式设置

设置项	描述	默认值	说明
`input_format_import_nested_json`	将嵌套 JSON 数据映射到嵌套表 (适用于 JSONEachRow 格式) 。	`false`
`input_format_json_read_bools_as_numbers`	允许在 JSON 输入格式中将布尔值作为数值解析。	`true`
`input_format_json_read_bools_as_strings`	允许在 JSON 输入格式中将布尔值解析为字符串。	`true`
`input_format_json_read_numbers_as_strings`	允许在 JSON 输入格式中将数值解析为字符串。	`true`
`input_format_json_read_arrays_as_strings`	允许在 JSON 输入格式中将 JSON 数组解析为字符串。	`true`
`input_format_json_read_objects_as_strings`	允许在 JSON input formats 中将 JSON objects 解析为字符串。	`true`
`input_format_json_named_tuples_as_objects`	将命名元组列解析为 JSON objects。	`true`
`input_format_json_try_infer_numbers_from_strings`	在进行 schema inference 时，尝试从字符串字段中推断数值。	`false`
`input_format_json_try_infer_named_tuples_from_objects`	在进行 schema 推断时，尝试从 JSON 对象中推断命名元组。	`true`
`input_format_json_infer_incomplete_types_as_strings`	在 JSON 输入格式中进行 schema 推断时，对于仅包含 NULL 或空对象/数组的键，使用 String 类型。	`true`
`input_format_json_defaults_for_missing_elements_in_named_tuple`	在解析命名元组时，为 JSON 对象中缺失的元素插入默认值。	`true`
`input_format_json_ignore_unknown_keys_in_named_tuple`	忽略命名元组中 JSON 对象里的未知键。	`false`
`input_format_json_compact_allow_variable_number_of_columns`	在 JSONCompact/JSONCompactEachRow 格式中允许列数可变，忽略多余列，并对缺失列使用默认值。	`false`
`input_format_json_throw_on_bad_escape_sequence`	如果 JSON 字符串包含错误的转义序列，则抛出异常。若禁用此项，错误的转义序列将按原样保留在数据中。	`true`
`input_format_json_empty_as_default`	将 JSON 输入中的空字段视为默认值。	`false`.	对于复杂的默认表达式，还必须同时启用 `input_format_defaults_for_omitted_fields`。
`output_format_json_quote_64bit_integers`	控制 JSON 输出格式中 64 位及以上整数是否加引号。	`false`
`output_format_json_quote_64bit_floats`	控制 JSON 输出格式中 64 位浮点数是否加引号。	`false`
`output_format_json_quote_denormals`	在 JSON 输出格式中启用 ‘+nan’、‘-nan’、‘+inf’ 和 ‘-inf’ 输出。	`false`
`output_format_json_quote_decimals`	控制在 JSON 输出格式中是否为 Decimal 输出加引号。	`false`
`output_format_json_escape_forward_slashes`	控制在 JSON 输出格式中是否对 String 输出中的正斜杠进行转义。	`true`
`output_format_json_named_tuples_as_objects`	将命名元组列序列化为 JSON 对象。	`true`
`output_format_json_array_of_rows`	以 JSONEachRow(Compact) 格式将所有行输出为 JSON 数组。	`false`
`output_format_json_validate_utf8`	启用对 JSON 输出格式中 UTF-8 序列的校验 (请注意，这不影响 JSON/JSONCompact/JSONColumnsWithMetadata 格式，它们始终会校验 UTF-8) 。	`false`

SQL 参考文档

数据类型

引擎

函数

格式

设置

系统表

数据湖

说明

示例用法

基础示例

输出

输入

插入数据

省略值的处理

查询数据

Nested 结构的用法

格式设置

​说明

​示例用法

​基础示例

​输出

​输入

​插入数据

​省略值的处理

​查询数据

​Nested 结构的用法

​格式设置

说明

示例用法

基础示例

输出

输入

插入数据

省略值的处理

查询数据

Nested 结构的用法

格式设置