JSON 函数

请注意:
下文中的一些示例引用自 ClickHouse 社区文档 并经过一定修改确保可以在 ByteHouse 中正常使用。

JSONExtract

Parses a JSON and extract a value of the given ByteHouse data type.
This is a generalization of the previous JSONExtract<type> functions.
This means
JSONExtract(..., 'String') returns exactly the same as JSONExtractString() ,
JSONExtract(..., 'Float64') returns exactly the same as JSONExtractFloat() .

Syntax

JSONExtract(json[, indices_or_keys…], Return_type)

Arguments

• json – json string.
• indices_or_keys - is a list of zero or more arguments each of them can be either string or integer.
  • String = access object member by key.
  • Positive integer = access the n-th member/key from the beginning.
  • Negative integer = access the n-th member/key from the end.
  • Minimum index of the element is 1. Thus the element 0 does not exist.
  • You may use integers to access both JSON arrays and JSON objects.
• Return_type – ByteHouse data type.

Returned value

• Extracted value of the given ByteHouse data type.

Example

SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'Tuple(String, Array(Float64))')

┌─JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'Tuple(String, Array(Float64))')─┐
│ (hello, [-1e+02, 2e+02, 3e+02])                                                         │
└─────────────────────────────────────────────────────────────────────────────────────────┘

SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 'Array(Nullable(Int8))')

┌─JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 'Array(Nullable(Int8))')─┐
│ [-100, ᴺᵁᴸᴸ, ᴺᵁᴸᴸ]                                                                   │
└──────────────────────────────────────────────────────────────────────────────────────┘

SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 4, 'Nullable(Int64)')

┌─JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 4, 'Nullable(Int64)')─┐
│ ᴺᵁᴸᴸ                                                                              │
└───────────────────────────────────────────────────────────────────────────────────┘

SELECT JSONExtract('{"passed": true}', 'passed', 'UInt8')

┌─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)')

┌─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)')

┌─JSONExtract('{"day": 5}', 'day', 'Enum8(\'Sunday\' = 0, \'Monday\' = 1, \'Tuesday\' = 2, \'Wednesday\' = 3, \'Thursday\' = 4, \'Friday\' = 5, \'Saturday\' = 6)')─┐
│ Friday                                                                                                                                                            │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

JSONExtractBool

Parses a JSON and extract a value. These functions are similar to visitParam functions.
If the value does not exist or has a wrong type, 0 will be returned.

Syntax

JSONExtractBool(json\[, indices_or_keys\]…) 

Arguments

• json – json string.
• indices_or_keys - is a list of zero or more arguments each of them can be either string or integer.
  • String = access object member by key.
  • Positive integer = access the n-th member/key from the beginning.
  • Negative integer = access the n-th member/key from the end.
  • Minimum index of the element is 1. Thus the element 0 does not exist.
  • You may use integers to access both JSON arrays and JSON objects.

Returned value

• UInt8.

Example

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

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

SELECT JSONExtractBool('{"passed": false}','passed')

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

JSONExtractFloat

Parses a JSON and extract a value. These functions are similar to visitParam functions.
If the value does not exist or has a wrong type, 0 will be returned.

Syntax

JSONExtractFloat(json\[, indices_or_keys\]…) 

Arguments

• json – json string.
• indices_or_keys - is a list of zero or more arguments each of them can be either string or integer.
  • String = access object member by key.
  • Positive integer = access the n-th member/key from the beginning.
  • Negative integer = access the n-th member/key from the end.
  • Minimum index of the element is 1. Thus the element 0 does not exist.
  • You may use integers to access both JSON arrays and JSON objects.

Returned value

• Float64.

Example

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

┌─JSONExtractFloat('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 2)─┐
│ 2e+02                                                               │
└─────────────────────────────────────────────────────────────────────┘

JSONExtractInt

Parses a JSON and extract a value. These functions are similar to visitParam functions.
If the value does not exist or has a wrong type, 0 will be returned.

Syntax

JSONExtractInt(json\[, indices_or_keys\]…) 

Arguments

• json – json string.
• indices_or_keys - is a list of zero or more arguments each of them can be either string or integer.
  • String = access object member by key.
  • Positive integer = access the n-th member/key from the beginning.
  • Negative integer = access the n-th member/key from the end.
  • Minimum index of the element is 1. Thus the element 0 does not exist.
  • You may use integers to access both JSON arrays and JSON objects.

Returned value

• Int64.

Example

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

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

JSONExtractKeysAndValues

Parses key-value pairs from a JSON where the values are of the given ByteHouse data type.

Syntax

JSONExtractKeysAndValues(json[, indices_or_keys…], Value_type)

Arguments

• json – json string.
• indices_or_keys - is a list of zero or more arguments each of them can be either string or integer.
  • String = access object member by key.
  • Positive integer = access the n-th member/key from the beginning.
  • Negative integer = access the n-th member/key from the end.
  • Minimum index of the element is 1. Thus the element 0 does not exist.
  • You may use integers to access both JSON arrays and JSON objects.
• Value_type - json value data type

Returned value

• key-value pairs

Example

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

┌─JSONExtractKeysAndValues('{"x": {"a": 5, "b": 7, "c": 11}}', 'x', 'Int8')─┐
│ [(a, 5), (b, 7), (c, 11)]                                                 │
└───────────────────────────────────────────────────────────────────────────┘

JSONExtractRaw

Returns a part of JSON as unparsed string.
If the part does not exist or has a wrong type, an empty string will be returned.

Syntax

JSONExtractRaw(json\[, indices_or_keys\]…)

Arguments

• json – json string.
• indices_or_keys - is a list of zero or more arguments each of them can be either string or integer.
  • String = access object member by key.
  • Positive integer = access the n-th member/key from the beginning.
  • Negative integer = access the n-th member/key from the end.
  • Minimum index of the element is 1. Thus the element 0 does not exist.
  • You may use integers to access both JSON arrays and JSON objects.

Returned value

• String

Example

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

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

JSONExtractString

Parse a JSON and extract a string. This function is similar to visitParamExtractString functions.
If the value does not exist or has a wrong type, an empty string will be returned.
The value is unescaped. If unescaping failed, it returns an empty string.

Syntax

JSONExtractString(json\[, indices_or_keys\]…)

Arguments

• json – json string.
• indices_or_keys - is a list of zero or more arguments each of them can be either string or integer.
  • String = access object member by key.
  • Positive integer = access the n-th member/key from the beginning.
  • Negative integer = access the n-th member/key from the end.
  • Minimum index of the element is 1. Thus the element 0 does not exist.
  • You may use integers to access both JSON arrays and JSON objects.

Returned value

• String

Example

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

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

SELECT JSONExtractString('{"abc":"\\u263a"}', 'abc')

┌─JSONExtractString('{"abc":"\\u263a"}', 'abc')─┐
│ ☺                                             │
└───────────────────────────────────────────────┘

SELECT JSONExtractString('{"abc":"\\u263"}', 'abc')

┌─JSONExtractString('{"abc":"\\u263"}', 'abc')─┐
│                                              │
└──────────────────────────────────────────────┘

SELECT JSONExtractString('{"abc":"hello}', 'abc')

┌─JSONExtractString('{"abc":"hello}', 'abc')─┐
│                                            │
└────────────────────────────────────────────┘

JSONExtractUInt

Parses a JSON and extract a value. These functions are similar to visitParam functions.
If the value does not exist or has a wrong type, 0 will be returned.

Syntax

JSONExtractUInt(json\[, indices_or_keys\]…) 

Arguments

• json – json string.
• indices_or_keys - is a list of zero or more arguments each of them can be either string or integer.
  • String = access object member by key.
  • Positive integer = access the n-th member/key from the beginning.
  • Negative integer = access the n-th member/key from the end.
  • Minimum index of the element is 1. Thus the element 0 does not exist.
  • You may use integers to access both JSON arrays and JSON objects.

Returned value

• UInt64.

Example

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

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

JSONHas

If the value exists in the JSON document, 1 will be returned.
If the value does not exist, 0 will be returned.

Syntax

JSONHas(json[, indices_or_keys]…)

Arguments

• json – json string.
• indices_or_keys - is a list of zero or more arguments each of them can be either string or integer.
  • String = access object member by key.
  • Positive integer = access the n-th member/key from the beginning.
  • Negative integer = access the n-th member/key from the end.
  • Minimum index of the element is 1. Thus the element 0 does not exist.
  • You may use integers to access both JSON arrays and JSON objects.

Returned value

• UInt8.

Example

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

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

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

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

JSONLength

Return the length of a JSON array or a JSON object.

If the value does not exist or has a wrong type, 0 will be returned.

Syntax

JSONLength(json\[, indices_or_keys\]…)

Arguments

• json – json string.
• indices_or_keys - is a list of zero or more arguments each of them can be either string or integer.
  • String = access object member by key.
  • Positive integer = access the n-th member/key from the beginning.
  • Negative integer = access the n-th member/key from the end.
  • Minimum index of the element is 1. Thus the element 0 does not exist.
  • You may use integers to access both JSON arrays and JSON objects.

Returned value

• UInt64.

Example

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

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

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

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

JSONType

Return the type of a JSON value.

If the value does not exist, Null will be returned.

Syntax

JSONType(json\[, indices_or_keys\]…)

Arguments

• json – json string.
• indices_or_keys - is a list of zero or more arguments each of them can be either string or integer.
  • String = access object member by key.
  • Positive integer = access the n-th member/key from the beginning.
  • Negative integer = access the n-th member/key from the end.
  • Minimum index of the element is 1. Thus the element 0 does not exist.
  • You may use integers to access both JSON arrays and JSON objects.

Returned value

• ByteHouse data type.

Example

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

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

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

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

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

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

visitParamExtractBool

Parses a true/false value. The result is UInt8.

Syntax

visitParamExtractBool(params, name)

Arguments

• params – json string.
• name - json key

Returned value

• UInt8.

Example

SELECT visitParamExtractBool('{"abc":true}', 'abc')

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

SELECT visitParamExtractBool('{"abc":false}', 'abc')

┌─visitParamExtractBool('{"abc":false}', 'abc')─┐
│ 0                                             │
└───────────────────────────────────────────────┘

visitParamExtractFloat

Parses a float value. The result is Float64.

Syntax

visitParamExtractFloat(params, name)

Arguments

• params – json string.
• name - json key

Returned value

• UInt8.

Example

SELECT visitParamExtractFloat('{"abc":123.0}', 'abc')

┌─visitParamExtractFloat('{"abc":123.1}', 'abc')─┐
│ 123.1                                          │
└────────────────────────────────────────────────┘

visitParamExtractInt

Parses a Int value. The result is Int64.

Syntax

visitParamExtractInt(params, name)

Arguments

• params – json string.
• name - json key

Returned value

• Int64.

Example

SELECT visitParamExtractInt('{"abc":123}', 'abc')

┌─visitParamExtractInt('{"abc":123}', 'abc')─┐
│ 123                                        │
└────────────────────────────────────────────┘

visitParamExtractRaw

Returns the value of a field, including separators.

Syntax

visitParamExtractRaw(params, name)

Arguments

• params – json string.
• name - json key

Returned value

• String.

Example

SELECT visitParamExtractRaw('{"abc":"\\n\\u0000"}', 'abc')

┌─visitParamExtractRaw('{"abc":"\\n\\u0000"}', 'abc')─┐
│ "\n\u0000"                                          │
└─────────────────────────────────────────────────────┘

SELECT visitParamExtractRaw('{"abc":{"def":[1,2,3]}}', 'abc')

┌─visitParamExtractRaw('{"abc":{"def":[1,2,3]}}', 'abc')─┐
│ {"def":[1,2,3]}                                        │
└────────────────────────────────────────────────────────┘

visitParamExtractString

Parses the string in double quotes. The value is unescaped. If unescaping failed, it returns an empty string.

Syntax

visitParamExtractString(params, name)

Arguments

• params – json string.
• name - json key

Returned value

• String.

Example

SELECT visitParamExtractString('{"abc":"\\u263a"}', 'abc')

┌─visitParamExtractString('{"abc":"\\u263a"}', 'abc')─┐
│ ☺                                                   │
└─────────────────────────────────────────────────────┘

SELECT visitParamExtractString('{"abc":"\\u263"}', 'abc')

┌─visitParamExtractString('{"abc":"\\u263"}', 'abc')─┐
│                                                    │
└────────────────────────────────────────────────────┘

SELECT visitParamExtractString('{"abc":"hello}', 'abc')

┌─visitParamExtractString('{"abc":"hello}', 'abc')─┐
│                                                  │
└──────────────────────────────────────────────────┘

There is currently no support for code points in the format \uXXXX\uYYYY that are not from the basic multilingual plane (they are converted to CESU-8 instead of UTF-8).

The following functions are based on simdjson designed for more complex JSON parsing requirements. The assumption 2 mentioned above still applies.

visitParamExtractUInt

Parses UInt64 from the value of the field named name . If this is a string field, it tries to parse a number from the beginning of the string. If the field does not exist, or it exists but does not contain a number, it returns 0.

Syntax

visitParamExtractUInt(params, name)

Arguments

• params – json string.
• name - json key

Returned value

• UInt64.

Example

SELECT visitParamExtractUInt('{"abc":2}', 'abc')

┌─visitParamExtractUInt('{"abc":2}', 'abc')─┐
│ 2                                         │
└───────────────────────────────────────────┘

visitParamHas

Checks whether there is a field with the name name.

Syntax

visitParamHas(params, name)

Arguments

• params – json string.
• name - json key

Returned value

• UInt8.

Example

SELECT visitParamHas('{"abc":"def"}', 'abc')

┌─visitParamHas('{"abc":"def"}', 'abc')─┐
│ 1                                     │
└───────────────────────────────────────┘