Skip to main content
Version: 0.2.0

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