- ClickHouse 支持的JSON函数
SELECT name FROM system.functions WHERE name LIKE '%JSON%' ;
-- 1.判断JSON是否合法
SELECT isValidJSON('{"a":1}') as isValid;
┌─isValid─┐
│ 1 │
└──────┘
--2. 判断JSON是否存在某个值
select JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') exist ;
┌─exist─┐
│ 1 │
└─────┘
-- 3.JSON 返回值的类型
SELECT
JSONType('{"a": "hello", "b": [-100, 200.0, 300]}', 'a') AS a_type,
JSONType('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') AS b_type;
┌─a_type─┬─b_type─┐
│ String │ Array │
└──────┴───────┘
--4.JSON 对象长度
select JSONLength('{"a": "hello", "b": [-100, 200.0, 300]}') as len;
┌─len─┐
│ 2 │
└────┘
--5.JSON 数组的长度
select JSONLength('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') as len;
┌─len─┐
│ 3 │
└────┘
--6.抽取JSON中返回值为String类型的值
SELECT
JSONExtractString('{"a": "hello", "b": [-100, 200.0, 300]}', 'a') AS a;
┌─a─────┐
│ hello │
└───────┘
SELECT JSONExtractRaw('{"a": "hello", "b": [-100, 200.0, 300]}', 'a') as a;
┌─a───────┐
│ "hello" │
└─────────┘
--7.抽取JSON中返回值为Float类型的值
SELECT JSONExtractFloat('{"a": "hello", "b": [-100, 200.0, 300],"f":1.01}', 'f') as f;
┌────f─┐
│ 1.01 │
└─────┘
--8.抽取JSON中返回值为Int类型的值
SELECT JSONExtractInt('{"a": "hello", "b": [-100, 200.0, 300],"i":100}','i') as i;
┌───i─┐
│ 100 │
└────┘
--9.抽取JSON中返回值为UInt类型的值(只能抽取无符号数)
SELECT JSONExtractUInt('{"a": "hello", "b": [-100, 200.0, 300],"i":-100}','i') as i0,
JSONExtractUInt('{"a": "hello", "b": [-100, 200.0, 300],"i":100}','i') as i;
┌─i0─┬────i─┐
│ 0 │ 100 │
└───┴─────┘
--10.返回JSON中Array中指定位置的值
SELECT
JSONExtractInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 1) AS index_1,
JSONExtractInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 2) AS index_2,
JSONExtractInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', -1) AS index_3;
┌─index_1─┬─index_2─┬─index_3─┐
│ -100 │ 200 │ 300 │
└───────┴───────┴───────┘
--11.抽取JSON中返回值为Array类型的值
SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 'Array(Int64)') AS B
┌─B──────────────┐
│ [-100,200,300] │
└────────────────┘
SELECT JSONExtractRaw('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') AS b;
┌─b──────────────┐
│ [-100,200,300] │
└────────────────┘
-- 12.抽取JSON的KEY
SELECT JSONKey('{"a": "hello", "b": [-100, 200.0, 300],"c":1}',1) AS k;
--13.JSONExtract 必须指定返回值的类型
SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300],"c":1}', 'c', 'Int64') AS value
┌─value─┐
│ 1 │
└─────┘
-- 14.JSON 解析键值对,值是给定的ClickHouse数据类型
SELECT JSONExtractKeysAndValues('{"x": {"a": 5, "b": 7, "c": 11}}', 'x', 'Int8') AS KV;
┌─KV─────────────────────────┐
│ [('a',5),('b',7),('c',11)] │
└────────────────────────────┘
官方文档 : https://clickhouse.tech/docs/en/sql-reference/functions/json-functions/