clickhouse输入输出格式之JSON系列【JSON、JSONCompact和JSONEachRow】

JSON

JSON格式的输入输出

JSON格式只支持数据的输出，不支持数据的解析（数据导入）。

案例演示

建表插数据

create table t_json_demo(id UInt8, prov String) ENGINE=TinyLog;
insert into t_json_demo values(1, 'jiangsu'),
(1, 'jiangsu'),
(2, 'anhui'),
(2, 'anihu'),
(3, 'beijing');

查询演示

以JSON格式查询

命令：select id,count() as cnt from t_json_demo group by id format JSON;

取出value的双引号

默认情况下， Int64和UInit64的整型使用双引号包裹， 如果要移除双引号，设置配置参数output_format_json_quote_64bit_integers为0。

命令：set output_format_json_quote_64bit_integers=0;

再进行同样的查询

命令：select id,count() as cnt from t_json_demo group by id format JSON;

查询时出现rows_before_limit_at_least

只有查询包含LIMIT时才输出， 只有在包含group by的语句中才有意义。当查询没有LIMIT时， 执行结果的最小行数。

设置显示极值的参数extremes=1（默认为0）

set extremes=1;

在进行查询
select id,count() as cnt from t_json_demo group by id format JSON;

 

JSONCompact

JSONCompact的输入输出

• JSON格式的数据以对象的方式输出， 而JSONCompact以数组的方式输出。
• JSONCompact只支持数据的查看， 不支持数据的导入。

案例演示

select id,count() as cnt from t_json_demo group by id format JSONCompact;

JSONEachRow

JSONEachRow数据输入输出

• 每行数据以换行符分隔的JSON对象。
• 支持数据的输出和数据导入。

案例演示

select id,count() as cnt from t_json_demo group by id format JSONEachRow;

数据导入

• 对象中键值对的顺序可任意排列。
• 缺失某些字段。

建表插入数据

#建表
create table UserActivity (PageViews UInt8, UserID String, Duration UInt64, Sign Int8) ENGINE TinyLog;
#插入数据
INSERT INTO UserActivity FORMAT JSONEachRow {"PageViews":5, "UserID":"4324182021466249494", "Duration":146,"Sign":-1} {"UserID":"4324182021466249494","PageViews":6,"Duration":185,"Sign":1}

缺失值处理演示

CREATE TABLE IF NOT EXISTS example_table
(
    x UInt32,
    a DEFAULT x + 2
) ENGINE = Memory;
# 第二条数据第二个值没有设置
insert into example_table FORMAT JSONEachRow {"x":3, "a":5} {"x":4};

参数

参数：input_format_defaults_for_omitted_fields
如果为0， 则x和a的默认值等于0（即UInt32数据类型的默认值）。
如果为1， 则x的默认值等于0， 但a的默认值等于x+2。
input_format_defaults_for_omitted_fields=1

嵌套数据结构

案例演示

建表插入数据

#建表
CREATE TABLE json_each_row_nested (n Nested (s String, i Int32) ) ENGINE = Memory;
# 插入数据
INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n.s": ["abc", "def"], "n.i": [1, 23]};
# 查看数据
select * from json_each_row_nested;

要将数据作为分层JSON对象插入

需要设置input_format_import_nested_json=1。（默认为0）

当参数设置为零时

当需要设置input_format_import_nested_json=0时：
INSERT INTO json_each_row_nested FORMAT JSONEachRow {“n”: {“s”: [“abc”, “def”], “i”: [1, 23]}}

Exception on client:
Code: 117. DB::Exception: Unknown field found while parsing JSONEachRow format: n

Connecting to 192.168.0.200:9000 as user default.
Connected to ClickHouse server version 20.1.4 revision 54431.