跳至主要内容

json-functions

有两组函数用于解析 JSON

simpleJSON (visitParam) 函数

ClickHouse 有专门的函数用于处理简化的 JSON。所有这些 JSON 函数都基于对 JSON 可以是什么的强烈假设。它们尝试尽可能少地做,以便尽可能快地完成任务。

做出以下假设

  1. 字段名称(函数参数)必须是常量。
  2. 字段名称以某种规范的方式在 JSON 中编码。例如:simpleJSONHas('{"abc":"def"}', 'abc') = 1,但 simpleJSONHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0
  3. 字段在任何嵌套级别上进行搜索,不加区分。如果有多个匹配字段,则使用第一个出现的字段。
  4. JSON 在字符串文字之外没有空格字符。

simpleJSONHas

检查是否存在名为 field_name 的字段。结果为 UInt8

语法

simpleJSONHas(json, field_name)

别名:visitParamHas

参数

返回值

  • 如果字段存在,则返回 1,否则返回 0UInt8.

示例

查询

CREATE TABLE jsons
(
`json` String
)
ENGINE = Memory;

INSERT INTO jsons VALUES ('{"foo":"true","qux":1}');

SELECT simpleJSONHas(json, 'foo') FROM jsons;
SELECT simpleJSONHas(json, 'bar') FROM jsons;

结果

1
0

simpleJSONExtractUInt

从名为 field_name 的字段的值中解析 UInt64。如果这是一个字符串字段,它会尝试从字符串的开头解析一个数字。如果字段不存在,或者存在但不包含数字,则返回 0

语法

simpleJSONExtractUInt(json, field_name)

别名:visitParamExtractUInt

参数

返回值

  • 如果字段存在且包含数字,则返回从字段解析的数字,否则返回 0UInt64.

示例

查询

CREATE TABLE jsons
(
`json` String
)
ENGINE = Memory;

INSERT INTO jsons VALUES ('{"foo":"4e3"}');
INSERT INTO jsons VALUES ('{"foo":3.4}');
INSERT INTO jsons VALUES ('{"foo":5}');
INSERT INTO jsons VALUES ('{"foo":"not1number"}');
INSERT INTO jsons VALUES ('{"baz":2}');

SELECT simpleJSONExtractUInt(json, 'foo') FROM jsons ORDER BY json;

结果

0
4
0
3
5

simpleJSONExtractInt

从名为 field_name 的字段的值中解析 Int64。如果这是一个字符串字段,它会尝试从字符串的开头解析一个数字。如果字段不存在,或者存在但不包含数字,则返回 0

语法

simpleJSONExtractInt(json, field_name)

别名:visitParamExtractInt

参数

返回值

  • 如果字段存在且包含数字,则返回从字段解析的数字,否则返回 0Int64.

示例

查询

CREATE TABLE jsons
(
`json` String
)
ENGINE = Memory;

INSERT INTO jsons VALUES ('{"foo":"-4e3"}');
INSERT INTO jsons VALUES ('{"foo":-3.4}');
INSERT INTO jsons VALUES ('{"foo":5}');
INSERT INTO jsons VALUES ('{"foo":"not1number"}');
INSERT INTO jsons VALUES ('{"baz":2}');

SELECT simpleJSONExtractInt(json, 'foo') FROM jsons ORDER BY json;

结果

0
-4
0
-3
5

simpleJSONExtractFloat

从名为 field_name 的字段的值中解析 Float64。如果这是一个字符串字段,它会尝试从字符串的开头解析一个数字。如果字段不存在,或者存在但不包含数字,则返回 0

语法

simpleJSONExtractFloat(json, field_name)

别名:visitParamExtractFloat

参数

返回值

  • 如果字段存在且包含数字,则返回从字段解析的数字,否则返回 0Float64.

示例

查询

CREATE TABLE jsons
(
`json` String
)
ENGINE = Memory;

INSERT INTO jsons VALUES ('{"foo":"-4e3"}');
INSERT INTO jsons VALUES ('{"foo":-3.4}');
INSERT INTO jsons VALUES ('{"foo":5}');
INSERT INTO jsons VALUES ('{"foo":"not1number"}');
INSERT INTO jsons VALUES ('{"baz":2}');

SELECT simpleJSONExtractFloat(json, 'foo') FROM jsons ORDER BY json;

结果

0
-4000
0
-3.4
5

simpleJSONExtractBool

从名为 field_name 的字段的值中解析真/假值。结果为 UInt8

语法

simpleJSONExtractBool(json, field_name)

别名:visitParamExtractBool

参数

返回值

如果字段的值为 true,则返回 1,否则返回 0。这意味着此函数将返回 0,包括(不仅仅是)以下情况

  • 如果字段不存在。
  • 如果字段包含 true 作为字符串,例如:{"field":"true"}
  • 如果字段包含 1 作为数值。

示例

查询

CREATE TABLE jsons
(
`json` String
)
ENGINE = Memory;

INSERT INTO jsons VALUES ('{"foo":false,"bar":true}');
INSERT INTO jsons VALUES ('{"foo":"true","qux":1}');

SELECT simpleJSONExtractBool(json, 'bar') FROM jsons ORDER BY json;
SELECT simpleJSONExtractBool(json, 'foo') FROM jsons ORDER BY json;

结果

0
1
0
0

simpleJSONExtractRaw

将名为 field_name 的字段的值作为 String 返回,包括分隔符。

语法

simpleJSONExtractRaw(json, field_name)

别名:visitParamExtractRaw

参数

返回值

  • 如果字段存在,则返回字段的值作为字符串,包括分隔符,否则返回空字符串。字符串

示例

查询

CREATE TABLE jsons
(
`json` String
)
ENGINE = Memory;

INSERT INTO jsons VALUES ('{"foo":"-4e3"}');
INSERT INTO jsons VALUES ('{"foo":-3.4}');
INSERT INTO jsons VALUES ('{"foo":5}');
INSERT INTO jsons VALUES ('{"foo":{"def":[1,2,3]}}');
INSERT INTO jsons VALUES ('{"baz":2}');

SELECT simpleJSONExtractRaw(json, 'foo') FROM jsons ORDER BY json;

结果


"-4e3"
-3.4
5
{"def":[1,2,3]}

simpleJSONExtractString

从名为 field_name 的字段的值中解析用双引号括起来的 String

语法

simpleJSONExtractString(json, field_name)

别名:visitParamExtractString

参数

返回值

  • 返回字段的未转义值作为字符串,包括分隔符。如果字段不包含双引号字符串,如果转义失败或如果字段不存在,则返回空字符串。字符串.

实现细节

目前不支持格式为 \uXXXX\uYYYY 的代码点,这些代码点不属于基本多语言平面(它们被转换为 CESU-8 而不是 UTF-8)。

示例

查询

CREATE TABLE jsons
(
`json` String
)
ENGINE = Memory;

INSERT INTO jsons VALUES ('{"foo":"\\n\\u0000"}');
INSERT INTO jsons VALUES ('{"foo":"\\u263"}');
INSERT INTO jsons VALUES ('{"foo":"\\u263a"}');
INSERT INTO jsons VALUES ('{"foo":"hello}');

SELECT simpleJSONExtractString(json, 'foo') FROM jsons ORDER BY json;

结果

\n\0



JSONExtract 函数

以下函数基于 simdjson,专为更复杂的 JSON 解析需求而设计。

isValidJSON

检查传递的字符串是否为有效的 JSON。

语法

isValidJSON(json)

示例

SELECT isValidJSON('{"a": "hello", "b": [-100, 200.0, 300]}') = 1
SELECT isValidJSON('not a json') = 0

JSONHas

如果该值存在于 JSON 文档中,则返回 1。如果该值不存在,则返回 0

语法

JSONHas(json [, indices_or_keys]...)

参数

  • json — 要解析的 JSON 字符串。字符串.
  • indices_or_keys — 零个或多个参数的列表,每个参数可以是字符串或整数。字符串Int*.

indices_or_keys 类型

  • 字符串 = 通过键访问对象成员。
  • 正整数 = 从开头访问第 n 个成员/键。
  • 负整数 = 从结尾访问第 n 个成员/键。

返回值

  • 如果该值存在于 json 中,则返回 1,否则返回 0UInt8.

示例

查询

SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') = 1
SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 4) = 0

元素的最小索引为 1。因此元素 0 不存在。您可以使用整数访问 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'

JSONLength

返回 JSON 数组或 JSON 对象的长度。如果该值不存在或类型错误,则返回 0

语法

JSONLength(json [, indices_or_keys]...)

参数

  • json — 要解析的 JSON 字符串。字符串.
  • indices_or_keys — 零个或多个参数的列表,每个参数可以是字符串或整数。字符串Int*.

indices_or_keys 类型

  • 字符串 = 通过键访问对象成员。
  • 正整数 = 从开头访问第 n 个成员/键。
  • 负整数 = 从结尾访问第 n 个成员/键。

返回值

  • 返回 JSON 数组或 JSON 对象的长度。如果该值不存在或类型错误,则返回 0UInt64.

示例

SELECT JSONLength('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') = 3
SELECT JSONLength('{"a": "hello", "b": [-100, 200.0, 300]}') = 2

JSONType

返回 JSON 值的类型。如果该值不存在,则返回 Null

语法

JSONType(json [, indices_or_keys]...)

参数

  • json — 要解析的 JSON 字符串。字符串.
  • indices_or_keys — 零个或多个参数的列表,每个参数可以是字符串或整数。字符串Int*.

indices_or_keys 类型

  • 字符串 = 通过键访问对象成员。
  • 正整数 = 从开头访问第 n 个成员/键。
  • 负整数 = 从结尾访问第 n 个成员/键。

返回值

  • 如果该值存在,则返回 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'

JSONExtractUInt

解析 JSON 并提取 UInt 类型的值。

语法

JSONExtractUInt(json [, indices_or_keys]...)

参数

  • json — 要解析的 JSON 字符串。字符串.
  • indices_or_keys — 零个或多个参数的列表,每个参数可以是字符串或整数。字符串Int*.

indices_or_keys 类型

  • 字符串 = 通过键访问对象成员。
  • 正整数 = 从开头访问第 n 个成员/键。
  • 负整数 = 从结尾访问第 n 个成员/键。

返回值

  • 如果该值存在,则返回 UInt 值,否则返回 NullUInt64.

示例

查询

SELECT JSONExtractUInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', -1) as x, toTypeName(x);

结果

┌───x─┬─toTypeName(x)─┐
│ 300 │ UInt64 │
└─────┴───────────────┘

JSONExtractInt

解析 JSON 并提取 Int 类型的值。

语法

JSONExtractInt(json [, indices_or_keys]...)

参数

  • json — 要解析的 JSON 字符串。字符串.
  • indices_or_keys — 零个或多个参数的列表,每个参数可以是字符串或整数。字符串Int*.

indices_or_keys 类型

  • 字符串 = 通过键访问对象成员。
  • 正整数 = 从开头访问第 n 个成员/键。
  • 负整数 = 从结尾访问第 n 个成员/键。

返回值

  • 如果该值存在,则返回 Int 值,否则返回 NullInt64.

示例

查询

SELECT JSONExtractInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', -1) as x, toTypeName(x);

结果

┌───x─┬─toTypeName(x)─┐
│ 300 │ Int64 │
└─────┴───────────────┘

JSONExtractFloat

解析 JSON 并提取 Int 类型的值。

语法

JSONExtractFloat(json [, indices_or_keys]...)

参数

  • json — 要解析的 JSON 字符串。字符串.
  • indices_or_keys — 零个或多个参数的列表,每个参数可以是字符串或整数。字符串Int*.

indices_or_keys 类型

  • 字符串 = 通过键访问对象成员。
  • 正整数 = 从开头访问第 n 个成员/键。
  • 负整数 = 从结尾访问第 n 个成员/键。

返回值

  • 如果该值存在,则返回 Float 值,否则返回 NullFloat64.

示例

查询

SELECT JSONExtractFloat('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 2) as x, toTypeName(x);

结果

┌───x─┬─toTypeName(x)─┐
│ 200 │ Float64 │
└─────┴───────────────┘

JSONExtractBool

解析 JSON 并提取布尔值。如果该值不存在或类型错误,则返回 0

语法

JSONExtractBool(json\[, indices_or_keys\]...)

参数

  • json — 要解析的 JSON 字符串。字符串.
  • indices_or_keys — 零个或多个参数的列表,每个参数可以是字符串或整数。字符串Int*.

indices_or_keys 类型

  • 字符串 = 通过键访问对象成员。
  • 正整数 = 从开头访问第 n 个成员/键。
  • 负整数 = 从结尾访问第 n 个成员/键。

返回值

  • 如果该值存在,则返回布尔值,否则返回 0布尔值.

示例

查询

SELECT JSONExtractBool('{"passed": true}', 'passed');

结果

┌─JSONExtractBool('{"passed": true}', 'passed')─┐
│ 1 │
└───────────────────────────────────────────────┘

JSONExtractString

解析 JSON 并提取字符串。此函数类似于 visitParamExtractString 函数。如果值不存在或类型错误,则返回空字符串。

语法

JSONExtractString(json [, indices_or_keys]...)

参数

  • json — 要解析的 JSON 字符串。字符串.
  • indices_or_keys — 零个或多个参数的列表,每个参数可以是字符串或整数。字符串Int*.

indices_or_keys 类型

  • 字符串 = 通过键访问对象成员。
  • 正整数 = 从开头访问第 n 个成员/键。
  • 负整数 = 从结尾访问第 n 个成员/键。

返回值

  • json 返回一个未转义的字符串。如果转义失败、值不存在或类型错误,则返回空字符串。 String.

示例

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') = ''

JSONExtract

解析 JSON 并提取给定 ClickHouse 数据类型的值。此函数是先前 JSONExtract<type> 函数的通用版本。意思是

JSONExtract(..., 'String') 返回与 JSONExtractString() 完全相同的结果,JSONExtract(..., 'Float64') 返回与 JSONExtractFloat() 完全相同的结果。

语法

JSONExtract(json [, indices_or_keys...], return_type)

参数

  • json — 要解析的 JSON 字符串。字符串.
  • indices_or_keys — 零个或多个参数的列表,每个参数可以是字符串或整数。字符串Int*.
  • return_type — 指定要提取的值类型的字符串。 String.

indices_or_keys 类型

  • 字符串 = 通过键访问对象成员。
  • 正整数 = 从开头访问第 n 个成员/键。
  • 负整数 = 从结尾访问第 n 个成员/键。

返回值

  • 如果存在指定返回类型的 value,则返回 value,否则根据指定的返回类型返回 0Null 或空字符串。 UInt64Int64Float64BoolString.

示例

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": "world"}', 'Map(String, String)') = map('a', 'hello', 'b', 'world');
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'

JSONExtractKeysAndValues

从 JSON 中解析键值对,其中值是给定 ClickHouse 数据类型的。

语法

JSONExtractKeysAndValues(json [, indices_or_keys...], value_type)

参数

  • json — 要解析的 JSON 字符串。字符串.
  • indices_or_keys — 零个或多个参数的列表,每个参数可以是字符串或整数。字符串Int*.
  • value_type — 指定要提取的值类型的字符串。 String.

indices_or_keys 类型

  • 字符串 = 通过键访问对象成员。
  • 正整数 = 从开头访问第 n 个成员/键。
  • 负整数 = 从结尾访问第 n 个成员/键。

返回值

  • 返回解析后的键值对数组。 Array(Tuple(value_type)).

示例

SELECT JSONExtractKeysAndValues('{"x": {"a": 5, "b": 7, "c": 11}}', 'x', 'Int8') = [('a',5),('b',7),('c',11)];

JSONExtractKeys

解析 JSON 字符串并提取键。

语法

JSONExtractKeys(json[, a, b, c...])

参数

  • json — 包含有效 JSON 的 String.
  • a, b, c... — 以逗号分隔的索引或键,指定嵌套 JSON 对象中内部字段的路径。每个参数可以是 String 以通过键获取字段,也可以是 Integer 以获取第 N 个字段(从 1 开始索引,负数从末尾开始计数)。如果未设置,则整个 JSON 将被解析为顶层对象。可选参数。

返回值

示例

查询

SELECT JSONExtractKeys('{"a": "hello", "b": [-100, 200.0, 300]}');

结果

text
┌─JSONExtractKeys('{"a": "hello", "b": [-100, 200.0, 300]}')─┐
│ ['a','b'] │
└────────────────────────────────────────────────────────────┘

JSONExtractRaw

返回 JSON 的一部分作为未解析的字符串。如果该部分不存在或类型错误,则返回空字符串。

语法

JSONExtractRaw(json [, indices_or_keys]...)

参数

  • json — 要解析的 JSON 字符串。字符串.
  • indices_or_keys — 零个或多个参数的列表,每个参数可以是字符串或整数。字符串Int*.

indices_or_keys 类型

  • 字符串 = 通过键访问对象成员。
  • 正整数 = 从开头访问第 n 个成员/键。
  • 负整数 = 从结尾访问第 n 个成员/键。

返回值

  • 返回 JSON 的一部分作为未解析的字符串。如果该部分不存在或类型错误,则返回空字符串。 String.

示例

SELECT JSONExtractRaw('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') = '[-100, 200.0, 300]';

JSONExtractArrayRaw

返回一个数组,其中包含 JSON 数组的元素,每个元素都表示为未解析的字符串。如果该部分不存在或不是数组,则返回空数组。

语法

JSONExtractArrayRaw(json [, indices_or_keys...])

参数

  • json — 要解析的 JSON 字符串。字符串.
  • indices_or_keys — 零个或多个参数的列表,每个参数可以是字符串或整数。字符串Int*.

indices_or_keys 类型

  • 字符串 = 通过键访问对象成员。
  • 正整数 = 从开头访问第 n 个成员/键。
  • 负整数 = 从结尾访问第 n 个成员/键。

返回值

  • 返回一个数组,其中包含 JSON 数组的元素,每个元素都表示为未解析的字符串。否则,如果该部分不存在或不是数组,则返回空数组。 Array(String).

示例

SELECT JSONExtractArrayRaw('{"a": "hello", "b": [-100, 200.0, "hello"]}', 'b') = ['-100', '200.0', '"hello"'];

JSONExtractKeysAndValuesRaw

从 JSON 对象中提取原始数据。

语法

JSONExtractKeysAndValuesRaw(json[, p, a, t, h])

参数

  • json — 包含有效 JSON 的 String.
  • p, a, t, h — 以逗号分隔的索引或键,指定嵌套 JSON 对象中内部字段的路径。每个参数可以是 string 以通过键获取字段,也可以是 integer 以获取第 N 个字段(从 1 开始索引,负数从末尾开始计数)。如果未设置,则整个 JSON 将被解析为顶层对象。可选参数。

返回值

示例

查询

SELECT JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}');

结果

┌─JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}')─┐
│ [('a','[-100,200]'),('b','{"c":{"d":"hello","f":"world"}}')] │
└──────────────────────────────────────────────────────────────────────────────────────────────┘

查询

SELECT JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}', 'b');

结果

┌─JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}', 'b')─┐
│ [('c','{"d":"hello","f":"world"}')] │
└───────────────────────────────────────────────────────────────────────────────────────────────────┘

查询

SELECT JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}', -1, 'c');

结果

┌─JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}', -1, 'c')─┐
│ [('d','"hello"'),('f','"world"')] │
└───────────────────────────────────────────────────────────────────────────────────────────────────────┘

JSON_EXISTS

如果该值存在于 JSON 文档中,则返回 1。如果该值不存在,则返回 0

语法

JSON_EXISTS(json, path)

参数

  • json — 包含有效 JSON 的字符串。 String.
  • path — 表示路径的字符串。 String.
注意

在 21.11 版本之前,参数顺序错误,即 JSON_EXISTS(path, json)

返回值

  • 如果 JSON 文档中存在值,则返回 1,否则返回 0

示例

SELECT JSON_EXISTS('{"hello":1}', '$.hello');
SELECT JSON_EXISTS('{"hello":{"world":1}}', '$.hello.world');
SELECT JSON_EXISTS('{"hello":["world"]}', '$.hello[*]');
SELECT JSON_EXISTS('{"hello":["world"]}', '$.hello[0]');

JSON_QUERY

解析 JSON 并提取值为 JSON 数组或 JSON 对象的值。如果值不存在,则返回空字符串。

语法

JSON_QUERY(json, path)

参数

  • json — 包含有效 JSON 的字符串。 String.
  • path — 表示路径的字符串。 String.
注意

在 21.11 版本之前,参数顺序错误,即 JSON_EXISTS(path, json)

返回值

  • 返回提取的值作为 JSON 数组或 JSON 对象。否则,如果值不存在,则返回空字符串。 String.

示例

查询

SELECT JSON_QUERY('{"hello":"world"}', '$.hello');
SELECT JSON_QUERY('{"array":[[0, 1, 2, 3, 4, 5], [0, -1, -2, -3, -4, -5]]}', '$.array[*][0 to 2, 4]');
SELECT JSON_QUERY('{"hello":2}', '$.hello');
SELECT toTypeName(JSON_QUERY('{"hello":2}', '$.hello'));

结果

["world"]
[0, 1, 4, 0, -1, -4]
[2]
String

JSON_VALUE

解析 JSON 并提取值为 JSON 标量的值。如果值不存在,则默认返回空字符串。

此函数由以下设置控制

  • 通过 SET function_json_value_return_type_allow_nullable = true,将返回 NULL。如果值为复杂类型(例如:struct、array、map),则默认返回空字符串。
  • 通过 SET function_json_value_return_type_allow_complex = true,将返回复杂值。

语法

JSON_VALUE(json, path)

参数

  • json — 包含有效 JSON 的字符串。 String.
  • path — 表示路径的字符串。 String.
注意

在 21.11 版本之前,参数顺序错误,即 JSON_EXISTS(path, json)

返回值

  • 如果存在,则返回提取的值作为 JSON 标量,否则返回空字符串。 String.

示例

查询

SELECT JSON_VALUE('{"hello":"world"}', '$.hello');
SELECT JSON_VALUE('{"array":[[0, 1, 2, 3, 4, 5], [0, -1, -2, -3, -4, -5]]}', '$.array[*][0 to 2, 4]');
SELECT JSON_VALUE('{"hello":2}', '$.hello');
SELECT toTypeName(JSON_VALUE('{"hello":2}', '$.hello'));
select JSON_VALUE('{"hello":"world"}', '$.b') settings function_json_value_return_type_allow_nullable=true;
select JSON_VALUE('{"hello":{"world":"!"}}', '$.hello') settings function_json_value_return_type_allow_complex=true;

结果

world
0
2
String

toJSONString

将值序列化为其 JSON 表示形式。支持各种数据类型和嵌套结构。默认情况下,64 位 整数 或更大(如 UInt64Int128)将用引号括起来。 output_format_json_quote_64bit_integers 控制此行为。特殊值 NaNinf 将被替换为 null。启用 output_format_json_quote_denormals 设置以显示它们。序列化 Enum 值时,函数将输出其名称。

语法

toJSONString(value)

参数

  • value — 要序列化的值。值可以是任何数据类型。

返回值

  • 值的 JSON 表示形式。 String.

示例

第一个示例显示了 Map 的序列化。第二个示例显示了包装在 Tuple 中的一些特殊值。

查询

SELECT toJSONString(map('key1', 1, 'key2', 2));
SELECT toJSONString(tuple(1.25, NULL, NaN, +inf, -inf, [])) SETTINGS output_format_json_quote_denormals = 1;

结果

{"key1":1,"key2":2}
[1.25,null,"nan","inf","-inf",[]]

另见

JSONArrayLength

返回最外层 JSON 数组中的元素数量。如果输入 JSON 字符串无效,则函数返回 NULL。

语法

JSONArrayLength(json)

别名:JSON_ARRAY_LENGTH(json)

参数

  • json — 包含有效 JSON 的 String.

返回值

  • 如果 json 是有效的 JSON 数组字符串,则返回数组元素的数量,否则返回 NULL。 Nullable(UInt64).

示例

SELECT
JSONArrayLength(''),
JSONArrayLength('[1,2,3]')

┌─JSONArrayLength('')─┬─JSONArrayLength('[1,2,3]')─┐
│ ᴺᵁᴸᴸ │ 3
└─────────────────────┴────────────────────────────┘

jsonMergePatch

返回合并后的 JSON 对象字符串,该字符串是由合并多个 JSON 对象形成的。

语法

jsonMergePatch(json1, json2, ...)

参数

  • json — 包含有效 JSON 的 String.

返回值

  • 如果 JSON 对象字符串有效,则返回合并后的 JSON 对象字符串。 String.

示例

SELECT jsonMergePatch('{"a":1}', '{"name": "joey"}', '{"name": "tom"}', '{"name": "zoey"}') AS res

┌─res───────────────────┐
│ {"a":1,"name":"zoey"} │
└───────────────────────┘

JSONAllPaths

返回存储在每一行 JSON 列中的所有路径列表。

语法

JSONAllPaths(json)

参数

返回值

示例

CREATE TABLE test (json JSON(max_dynamic_paths=1)) ENGINE = Memory;
INSERT INTO test FORMAT JSONEachRow {"json" : {"a" : 42}}, {"json" : {"b" : "Hello"}}, {"json" : {"a" : [1, 2, 3], "c" : "2020-01-01"}}
SELECT json, JSONAllPaths(json) FROM test;
┌─json─────────────────────────────────┬─JSONAllPaths(json)─┐
│ {"a":"42"} │ ['a'] │
│ {"b":"Hello"} │ ['b'] │
│ {"a":["1","2","3"],"c":"2020-01-01"} │ ['a','c'] │
└──────────────────────────────────────┴────────────────────┘

JSONAllPathsWithTypes

返回存储在每一行 JSON 列中的所有路径及其数据类型映射。

语法

JSONAllPathsWithTypes(json)

参数

返回值

示例

CREATE TABLE test (json JSON(max_dynamic_paths=1)) ENGINE = Memory;
INSERT INTO test FORMAT JSONEachRow {"json" : {"a" : 42}}, {"json" : {"b" : "Hello"}}, {"json" : {"a" : [1, 2, 3], "c" : "2020-01-01"}}
SELECT json, JSONAllPathsWithTypes(json) FROM test;
┌─json─────────────────────────────────┬─JSONAllPathsWithTypes(json)───────────────┐
│ {"a":"42"} │ {'a':'Int64'} │
│ {"b":"Hello"} │ {'b':'String'} │
│ {"a":["1","2","3"],"c":"2020-01-01"} │ {'a':'Array(Nullable(Int64))','c':'Date'} │
└──────────────────────────────────────┴───────────────────────────────────────────┘

JSONDynamicPaths

返回存储在每一行 JSON 列中作为单独子列的动态路径列表。

语法

JSONDynamicPaths(json)

参数

返回值

示例

CREATE TABLE test (json JSON(max_dynamic_paths=1)) ENGINE = Memory;
INSERT INTO test FORMAT JSONEachRow {"json" : {"a" : 42}}, {"json" : {"b" : "Hello"}}, {"json" : {"a" : [1, 2, 3], "c" : "2020-01-01"}}
SELECT json, JSONDynamicPaths(json) FROM test;
┌─json─────────────────────────────────┬─JSONDynamicPaths(json)─┐
| {"a":"42"} │ ['a'] │
│ {"b":"Hello"} │ [] │
│ {"a":["1","2","3"],"c":"2020-01-01"} │ ['a'] │
└──────────────────────────────────────┴────────────────────────┘

JSONDynamicPathsWithTypes

返回存储在每一行 JSON 列中作为单独子列的动态路径及其类型的映射。

语法

JSONAllPathsWithTypes(json)

参数

返回值

示例

CREATE TABLE test (json JSON(max_dynamic_paths=1)) ENGINE = Memory;
INSERT INTO test FORMAT JSONEachRow {"json" : {"a" : 42}}, {"json" : {"b" : "Hello"}}, {"json" : {"a" : [1, 2, 3], "c" : "2020-01-01"}}
SELECT json, JSONDynamicPathsWithTypes(json) FROM test;
┌─json─────────────────────────────────┬─JSONDynamicPathsWithTypes(json)─┐
│ {"a":"42"} │ {'a':'Int64'} │
│ {"b":"Hello"} │ {} │
│ {"a":["1","2","3"],"c":"2020-01-01"} │ {'a':'Array(Nullable(Int64))'} │
└──────────────────────────────────────┴─────────────────────────────────┘

JSONSharedDataPaths

返回存储在每一行 JSON 列中共享数据结构中的路径列表。

语法

JSONSharedDataPaths(json)

参数

返回值

示例

CREATE TABLE test (json JSON(max_dynamic_paths=1)) ENGINE = Memory;
INSERT INTO test FORMAT JSONEachRow {"json" : {"a" : 42}}, {"json" : {"b" : "Hello"}}, {"json" : {"a" : [1, 2, 3], "c" : "2020-01-01"}}
SELECT json, JSONSharedDataPaths(json) FROM test;
┌─json─────────────────────────────────┬─JSONSharedDataPaths(json)─┐
│ {"a":"42"} │ [] │
│ {"b":"Hello"} │ ['b'] │
│ {"a":["1","2","3"],"c":"2020-01-01"} │ ['c'] │
└──────────────────────────────────────┴───────────────────────────┘

JSONSharedDataPathsWithTypes

返回存储在每一行 JSON 列中共享数据结构中的路径及其类型的映射。

语法

JSONSharedDataPathsWithTypes(json)

参数

返回值

示例

CREATE TABLE test (json JSON(max_dynamic_paths=1)) ENGINE = Memory;
INSERT INTO test FORMAT JSONEachRow {"json" : {"a" : 42}}, {"json" : {"b" : "Hello"}}, {"json" : {"a" : [1, 2, 3], "c" : "2020-01-01"}}
SELECT json, JSONSharedDataPathsWithTypes(json) FROM test;
┌─json─────────────────────────────────┬─JSONSharedDataPathsWithTypes(json)─┐
│ {"a":"42"} │ {} │
│ {"b":"Hello"} │ {'b':'String'} │
│ {"a":["1","2","3"],"c":"2020-01-01"} │ {'c':'Date'} │
└──────────────────────────────────────┴────────────────────────────────────┘