clickhouse 22版本中新添加了一种字段类型: json， 存储JavaScript Object Notation (JSON) documents 在单个字段中
JSON字段类型目前还是一个实验特性，如果启用，需要设置：allow_experimental_object_type = 1

本文讲解json字段类型如何使用，将基于json相关的函数如何进行行列转化、字段提取及解析json数组

1. json字段类型简单示例

set allow_experimental_object_type = 1;
CREATE TABLE json(
    name String,
    num UInt32,
    o JSON
) ENGINE = Memory;

INSERT INTO json VALUES ('test1',1,'{"a": 11, "b": { "c": 2, "d": [1, 2, 3] }}');
INSERT INTO json VALUES ('test2',2,'{"a": 12, "b": { "c": 2, "d": [1, 2, 3] }}');

SELECT o.a as oa, o.b.c as obc, o.b.d[3] as d3 FROM json;

--按json格式输出
SET output_format_json_named_tuples_as_objects = 1
SELECT * FROM json FORMAT JSONEachRow

2. json相关的函数

clickhouse操作了很多json相关的函数，详细参见json-functions

行列转换详见：clickhouse–玩转行列转换

2.1. 列转行

这里重点讲解将 json数组对象的列 转化为多行

• 初始化数据

CREATE TABLE json(
    name String,
    num UInt32,
    o String
) ENGINE = Memory;

INSERT INTO json VALUES ('广州',3,'[{"name":"天河","model":"M779011"}, {"name":"荔湾","model":"M669011"}, {"name":"越秀","model":"M559011"}]');
INSERT INTO json VALUES ('北京',2,'[{"name":"海淀","model":"M779011"}, {"name":"昌平","model":"M669011"}]');

• 数据查询SQL

select name,num,ele from json array join JSONExtractArrayRaw(o) as ele ;

select name,num,arrayJoin(JSONExtractArrayRaw(o)) as ele from json;
--获取json对象的某一个属性
select name,num,JSONExtractRaw(ele,'name') from json array join JSONExtractArrayRaw(o) as ele ;

2.2. 行转列

这里重点讲解将多行数据转化为列，并按json string格式返回。

• 初始化数据

CREATE TABLE t_gaokao_score  (
  id UInt32 NOT NULL,
  student_name String,
  subject String,
  score Float64
) ENGINE = MergeTree()
ORDER BY id;

INSERT INTO t_gaokao_score VALUES 
(1, '林磊儿', '语文', 148),
(2, '林磊儿', '数学', 150),
(3, '林磊儿', '英语', 147),
(4, '乔英子', '语文', 121),
(5, '乔英子', '数学', 106),
(6, '乔英子', '英语', 146),
(7, '方一凡', '语文', 70),
(8, '方一凡', '数学', 90),
(9, '方一凡', '英语', 59),
(10, '方一凡', '特长加分', 200),
(11, '陈哈哈', '语文', 109),
(12, '陈哈哈', '数学', 92),
(13, '陈哈哈', '英语', 80);

• 数据查询SQL

select subject, groupArray(10)(score) as scores,avg(score) as avg,max(score) as max from t_gaokao_score group by subject;

select subject, groupArray(10)(concat(student_name,':',toString(score))) as scores,avg(score) as avg from t_gaokao_score group by subject;

select subject, toJSONString(groupArray(10)(concat(student_name,':',toString(score)))) as scores,avg(score) as avg from t_gaokao_score group by subject;

2.3. 字段提取

SELECT 
    visitParamExtractBool('{"name":true}', 'name') AS bool, 
    visitParamExtractInt('{"name":123}', 'name') AS int, 
    visitParamExtractFloat('{"name":0.1}', 'name') AS float, 
    visitParamExtractString('{"name":"你好"}', 'name') AS str, 
    visitParamExtractRaw('{"name":"你好"}', 'name') AS raw

2.4. 解析json数组

SELECT 
    visitParamExtractString(json, 'name') AS name, 
    visitParamExtractString(json, 'model') AS model, 
    num,
    ele
FROM 
(
    WITH 
        '[{"name":"天河","model":"M779011"}, {"name":"荔湾","model":"M669011"}, {"name":"越秀","model":"M559011"}]' AS json, 
        3 AS num
    SELECT 
        json, 
        num, 
        JSONExtractArrayRaw(json) AS arr, 
        arrayJoin(arr) AS ele
)