跳到主要内容
版本：0.2.x

JSON

Notice: Some of the examples below are referenced from ClickHouse Documentation but have been adapted and modified to work in ByConity.

JSONExtract​

Parses a JSON and extract a value of the given ByConity 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 – ByConity data type.

Returned value

  • Extracted value of the given ByConity 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 ByConity 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

  • ByConity 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                                     │
└───────────────────────────────────────┘