JSON Functions

Json functions are used to manipulate data of string type.

JSONExtractString

Parses a JSON and extract a string. 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.

JSONExtractString(json[, indices_or_keys]…)

Examples

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

toJSONString

Serializes a value to its JSON representation. Various data types and nested structures are supported. 64-bit integers or bigger (like UInt64 or Int128) are enclosed in quotes by default. Special values NaN and inf are replaced with null. When serializing an Enum value, the function outputs its name.

toJSONString(value)

Arguments

• value — Value to serialize. Value may be of any data type.

Returned value

• JSON representation of the value.
• Type: String

Example

SELECT toJSONString(‘a:b’) = "a:b" 
SELECT toJSONString(123456) = 123456
SELECT toJSONString(0x64) = 100

JSONExtractArrayRaw

Returns an array with elements of JSON array, each represented as unparsed string. If the part does not exist or isn’t array, an empty array will be returned.

JSONExtractArrayRaw(json[, indices_or_keys…])

Example

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

INSERT INTO ChatArXivSELECT 
            abstract, 
            id, 
            vector, 
            metadata, 
            parseDateTimeBestEffort(JSONExtractString(toJSONString(metadata), 'pubdate')) AS pubdate, 
            JSONExtractString(toJSONString(metadata), 'title') AS title, 
            arrayMap(x->trim(BOTH '"' FROM x), 
            JSONExtractArrayRaw(toJSONString(metadata), 'categories')) AS categories, 
            arrayMap(x->trim(BOTH '"' FROM x), 
            JSONExtractArrayRaw(toJSONString(metadata), 'authors')) AS authors, 
            JSONExtractString(toJSONString(metadata), 'comment') AS comment, 
            JSONExtractString(toJSONString(metadata), 'primary_category') AS primary_category
FROM s3( 'https://myscale-demo.s3.ap-southeast-1.amazonaws.com/chat_arxiv/data.part*.zst', 'JSONEachRow', 'abstract String, id String, vector Array(Float32), metadata Object(''JSON'')', 'zstd' )