二十二、clickhouse的json函数
在Yandex.Metrica中，用户使用JSON作为访问参数。为了处理这些JSON，实现了一些函数。（尽管在大多数情况下，JSON是预先进行额外处理的，并将结果值放在单独的列中。）所有的这些函数都进行了尽可能的假设。以使函数能够尽快的完成工作。
我们对JSON格式做了如下假设：
字段名称（函数的参数）必须使常量。
字段名称必须使用规范的编码。例如：visitParamHas('{"abc":"def"}', 'abc') = 1，但是 visitParamHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0
函数可以随意的在多层嵌套结构下查找字段。如果存在多个匹配字段，则返回第一个匹配字段。
JSON除字符串文本外不存在空格字符。
--1.visitParamHas(参数，名称)
检查是否存在«name»名称的字段
--2.visitParamExtractUInt(参数，名称)
将名为«name»的字段的值解析成UInt64。如果这是一个字符串字段，函数将尝试从字符串的开头解析一个数字。如果该字段不存在，或无法从它中解析到数字，则返回0。
--3.visitParamExtractInt(参数，名称)
与visitParamExtractUInt相同，但返回Int64。
--4.visitParamExtractFloat(参数，名称)
与visitParamExtractUInt相同，但返回Float64。
--5.visitParamExtractBool(参数，名称)
解析true/false值。其结果是UInt8类型的。
--6.visitParamExtractRaw(参数，名称)
返回字段的值，包含空格符。
示例:
visitParamExtractRaw('{"abc":"\\n\\u0000"}', 'abc') = '"\\n\\u0000"'
visitParamExtractRaw('{"abc":{"def":[1,2,3]}}', 'abc') = '{"def":[1,2,3]}'
visitParamExtractString(参数，名称)
使用双引号解析字符串。这个值没有进行转义。如果转义失败，它将返回一个空白字符串。
示例:
visitParamExtractString('{"abc":"\\n\\u0000"}', 'abc') = '\n\0'
visitParamExtractString('{"abc":"\\u263a"}', 'abc') = '☺'
visitParamExtractString('{"abc":"\\u263"}', 'abc') = ''
visitParamExtractString('{"abc":"hello}', 'abc') = ''
目前不支持\uXXXX\uYYYY这些字符编码，这些编码不在基本多文种平面中（它们被转化为CESU-8而不是UTF-8）。
以下函数基于simdjson，专为更复杂的JSON解析要求而设计。但上述假设2仍然适用。
--7.JSONHas(json[, indices_or_keys]…)
如果JSON中存在该值，则返回1。
如果该值不存在，则返回0。
示例：
select JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') = 1 select JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 4) = 0
indices_or_keys可以是零个或多个参数的列表，每个参数可以是字符串或整数。
String = 按成员名称访问JSON对象成员。
正整数 = 从头开始访问第n个成员/成员名称。
负整数 = 从末尾访问第n个成员/成员名称。
您可以使用整数来访问JSON数组和JSON对象。
例如：
select JSONExtractKey('{"a": "hello", "b": [-100, 200.0, 300]}', 1) = 'a' select JSONExtractKey('{"a": "hello", "b": [-100, 200.0, 300]}', 2) = 'b' select JSONExtractKey('{"a": "hello", "b": [-100, 200.0, 300]}', -1) = 'b' select JSONExtractKey('{"a": "hello", "b": [-100, 200.0, 300]}', -2) = 'a' select JSONExtractString('{"a": "hello", "b": [-100, 200.0, 300]}', 1) = 'hello'
--8.JSONLength(json[, indices_or_keys]…)
返回JSON数组或JSON对象的长度。
如果该值不存在或类型错误，将返回0。
示例：
select JSONLength('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') = 3 select JSONLength('{"a": "hello", "b": [-100, 200.0, 300]}') = 2
--9.JSONType(json[, indices_or_keys]…)
返回JSON值的类型。
如果该值不存在，将返回Null。
示例：
select JSONType('{"a": "hello", "b": [-100, 200.0, 300]}') = 'Object' select JSONType('{"a": "hello", "b": [-100, 200.0, 300]}', 'a') = 'String' select JSONType('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') = 'Array'
--10.JSONExtractUInt(json[, indices_or_keys]…)
JSONExtractInt(json[, indices_or_keys]…)
JSONExtractFloat(json[, indices_or_keys]…)
JSONExtractBool(json[, indices_or_keys]…)
解析JSON并提取值。这些函数类似于visitParam*函数。
如果该值不存在或类型错误，将返回0。
示例:
select JSONExtractInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 1) = -100 select JSONExtractFloat('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 2) = 200.0 select JSONExtractUInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', -1) = 300
--11.JSONExtractString(json[, indices_or_keys]…)
解析JSON并提取字符串。此函数类似于visitParamExtractString函数。
如果该值不存在或类型错误，则返回空字符串。
该值未转义。如果unescaping失败，则返回一个空字符串。
示例:
select JSONExtractString('{"a": "hello", "b": [-100, 200.0, 300]}', 'a') = 'hello' select JSONExtractString('{"abc":"\\n\\u0000"}', 'abc') = '\n\0' select JSONExtractString('{"abc":"\\u263a"}', 'abc') = '☺' select JSONExtractString('{"abc":"\\u263"}', 'abc') = '' select JSONExtractString('{"abc":"hello}', 'abc') = ''
--12.JSONExtract(json[, indices_or_keys…], Return_type)
解析JSON并提取给定ClickHouse数据类型的值。
这是以前的JSONExtract<type>函数的变体。 这意味着JSONExtract(…, ‘String’)返回与JSONExtractString()返回完全相同。JSONExtract(…, ‘Float64’)返回于JSONExtractFloat()`返回完全相同。
示例:
SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'Tuple(String, Array(Float64))') = ('hello',[-100,200,300]) SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'Tuple(b Array(Float64), a String)') = ([-100,200,300],'hello') SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 'Array(Nullable(Int8))') = [-100, NULL, NULL] SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 4, 'Nullable(Int64)') = NULL SELECT JSONExtract('{"passed": true}', 'passed', 'UInt8') = 1 SELECT JSONExtract('{"day": "Thursday"}', 'day', 'Enum8(\'Sunday\' = 0, \'Monday\' = 1, \'Tuesday\' = 2, \'Wednesday\' = 3, \'Thursday\' = 4, \'Friday\' = 5, \'Saturday\' = 6)') = 'Thursday' SELECT JSONExtract('{"day": 5}', 'day', 'Enum8(\'Sunday\' = 0, \'Monday\' = 1, \'Tuesday\' = 2, \'Wednesday\' = 3, \'Thursday\' = 4, \'Friday\' = 5, \'Saturday\' = 6)') = 'Friday'
--13.JSONExtractKeysAndValues(json[, indices_or_keys…], Value_type)
从JSON中解析键值对，其中值是给定的ClickHouse数据类型。
示例：
SELECT JSONExtractKeysAndValues('{"x": {"a": 5, "b": 7, "c": 11}}', 'x', 'Int8') = [('a',5),('b',7),('c',11)];
--14JSONExtractRaw(json[, indices_or_keys]…)
返回JSON的部分。
如果部件不存在或类型错误，将返回空字符串。
示例:
select JSONExtractRaw('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') = '[-100, 200.0, 300]'
示例应用查询：
WITH '[{"name":"天台","tall":100,"model":"M779011"},{"name":"楼顶","tall":90,"model":"M669011"}]' AS new, 'S123' AS num SELECT new, num ┌─new────────────────────────────────────────────────────────────────────────────────────────┬─num──┐ │ [{"name":"天台","tall":100,"model":"M779011"},{"name":"楼顶","tall":90,"model":"M669011"}] │ S123 │ └────────────────────────────────────────────────────────────────────────────────────────────┴──────┘ 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 ┌─bool─┬─int─┬─float─┬─str──┬─raw────┐ │ 1 │ 123 │ 0.1 │ 你好 │ "你好" │ └──────┴─────┴───────┴──────┴────────┘ --使用JSONExtractArrayRaw()函数，将字符串转化为json数组： SELECT visitParamExtractString(json, 'name') AS name, visitParamExtractInt(json, 'tall') AS tall, visitParamExtractString(json, 'model') AS model, num FROM ( WITH '[{"name":"天台","tall":100,"model":"M779011"}, {"name":"楼顶","tall":90,"model":"M669011"}, {"name":"秀儿","tall":80,"model":"M559011"}]' AS new, 'S123' AS num SELECT new, num, JSONExtractArrayRaw(new) AS arr, arrayJoin(arr) AS json ) ┌─name─┬─tall─┬─model───┬─num──┐ │ 天台 │ 100 │ M779011 │ S123 │ │ 楼顶 │ 90 │ M669011 │ S123 │ │ 秀儿 │ 80 │ M559011 │ S123 │ └──────┴──────┴─────────┴──────┘ --可以使用字符截取： WITH '[{"name":"天台","tall":100,"model":"M779011"}, {"name":"楼顶","tall":90,"model":"M669011"}, {"name":"秀儿","tall":80,"model":"M559011"}]' AS new, replaceAll(replaceAll(new, '[', ''), ']', '') AS out, concat(arrayJoin(splitByString('},', out)), '}') AS json, 'S123' AS num SELECT visitParamExtractString(json, 'name') AS name, visitParamExtractInt(json, 'tall') AS tall, visitParamExtractString(json, 'model') AS model, num ┌─name─┬─tall─┬─model───┬─num──┐ │ 天台 │ 100 │ M779011 │ S123 │ │ 楼顶 │ 90 │ M669011 │ S123 │ │ 秀儿 │ 80 │ M559011 │ S123 │ └──────┴──────┴─────────┴──────┘
