Tencent Cloud

Recent Pages

JSON Functions

마지막 업데이트 시간:2024-01-22 10:52:48

This document introduces the basic syntax and examples of JSON functions.
Function
Syntax
Description
json_array_contains
json_array_contains(x, value)
Determines whether a JSON array contains a given value.
json_array_get
json_array_get(x, index)
Returns the element with the specified index in a given JSON array.
json_array_length
json_array_length(x) 
Returns the number of elements in a given JSON array. If `x` is not a JSON array, `null` will be returned.
json_extract
json_extract(x, json_path)
Extracts a set of JSON values (array or object) from a JSON object or array.
json_extract_scalar
json_extract_scalar(x, json_path)
Extracts a set of scalar values (strings, integers, or Boolean values) from a JSON object or array. Similar to the `json_extract` function.
json_format
json_format(x)
Converts a JSON value into a string value.
json_parse
json_parse(x)
Converts a string value into a JSON value.
json_size
json_size(x, json_path)
Calculates the number of elements in a JSON object or array.
json_array_contains
The json_array_contains function is used to determine whether a JSON array contains a specified value.
Syntax
json_array_contains(x, value)
Parameter description
Parameter
Description
x
The parameter value is a JSON array.
value
Value.
Return value type
Boolean
Example
Determine whether the JSON array [1, 2, 3] contains 2.
Search and analysis statement
* | SELECT json_array_contains('[1, 2, 3]', 2)
Search and analysis result
TRUE
json_array_get
The json_array_get function is used to get the element with a specified index in a JSON array.
Syntax
json_array_get(x, index)
Parameter description
Parameter
Description
x
The parameter value is a JSON array.
index
JSON subscript (index), starting from 0.
Return value type
Varchar
Example
Return the element with index 1 in the JSON array ["a", [3, 9], "c"].
Search and analysis statement
* | SELECT json_array_get('["a", [3, 9], "c"]', 1)
Search and analysis result
[3,9]
json_array_length
The json_array_length function is used to calculate the number of elements in a JSON array. If x is not a JSON array, null will be returned.
Syntax
json_array_length(x)
Parameter description
Parameter
Description
x
The parameter value is a JSON array.
Return value type
Bigint
Example
Example 1. Calculate the number of JSON elements in the apple.message field
apple.message:[{"traceName":"StoreMonitor"},{"topicName":"persistent://apache/pulsar/test-partition-17"},{"producerName":"pulsar-mini-338-36"},{"localAddr":"pulsar://pulsar-mini-broker-5.pulsar-mini-broker.pulsar.svc.cluster.local:6650"},{"sequenceId":826},{"storeTime":1635905306062},{"messageId":"19422-24519"},{"status":"SUCCESS"}]
Search and analysis statement
* | SELECT json_array_length(apple.message)
Search and analysis result
8
json_extract
The json_extract function is used to extract a set of JSON values (array or object) from a JSON object or array.
Syntax
json_extract(x, json_path)
Parameter description
Parameter
Description
x
The parameter value is a JSON object or array.
json_path
﻿JSONPath, such as $.store.book[0].title.
Note: JSON syntax requiring array element traversal is not supported, such as the following: $.store.book[*].author, $..book[(@.length-1)], $..book[?(@.price<10)].
Return value type
JSON string
Example
Get the value of epochSecond in the apple.instant field.
Field sample
apple.instant:{"epochSecond":1635905306,"nanoOfSecond":63001000}
Search and analysis statement
* | SELECT json_extract(apple.instant, '$.epochSecond')
Search and analysis result
1635905306
json_extract_scalar
The json_extract_scalar function is used to extract a set of scalar values (strings, integers, or Boolean values) from a JSON object or array.
Syntax
json_extract_scalar(x, json_path)
Parameter description
Parameter
Description
x
The parameter value is a JSON array.
json_path
﻿JSONPath, such as $.store.book[0].title.
Note: JSON syntax requiring array element traversal is not supported, such as the following: $.store.book[*].author, $..book[(@.length-1)], $..book[?(@.price<10)].
Return value type
Varchar
Example
Get the value of epochSecond from the apple.instant field and convert the value into a bigint value for summation.
Field sample
apple.instant:{"epochSecond":1635905306,"nanoOfSecond":63001000}
Search and analysis statement
* | SELECT sum(cast(json_extract_scalar(apple.instant,'$.epochSecond') AS bigint) )
Search and analysis result
1635905306
json_format
The json_format function is used to convert a JSON value into a string value.
Syntax
json_format(x)
Parameter description
Parameter
Description
x
The parameter value is of JSON type.
Return value type
Varchar
Example
Convert the JSON array [1,2,3] into a string [1, 2, 3].
Search and analysis statement
* | SELECT json_format(json_parse('[1, 2, 3]'))
Search and analysis result
[1, 2, 3]
json_parse
The json_parse function is used to convert a string value into a JSON value and determine whether it complies with the JSON format.
Syntax
json_parse(x)
Parameter description
Parameter
Description
x
The parameter value is a string.
Return value type
JSON
Example
Convert the JSON array [1,2,3] into a string [1, 2, 3].
Search and analysis statement
* | SELECT json_parse('[1, 2, 3]')
Search and analysis result
[1, 2, 3]
json_size
The json_size function is used to calculate the number of elements in a JSON object or array.
Syntax
json_size(x, json_path)
Parameter description
Parameter
Description
x
The parameter value is a JSON object or array.
json_path
JSON path, in the format of $.store.book[0].title.
Return value type
Bigint
Example
Convert the JSON array [1,2,3] into a string [1, 2, 3].
Search and analysis statement
* | SELECT json_size(json_parse('[1, 2, 3]'),'$')
Search and analysis result
3
﻿

문의하기

고객의 업무에 전용 서비스를 제공해드립니다.

기술 지원

더 많은 도움이 필요하시면, 티켓을 통해 연락 바랍니다. 티켓 서비스는 연중무휴 24시간 제공됩니다.

연중무휴 24시간 전화 지원

Function	Syntax	Description
json_array_contains	json_array_contains(x, value)	Determines whether a JSON array contains a given value.
json_array_get	json_array_get(x, index)	Returns the element with the specified index in a given JSON array.
json_array_length	json_array_length(x)	Returns the number of elements in a given JSON array. If `x` is not a JSON array, `null` will be returned.
json_extract	json_extract(x, json_path)	Extracts a set of JSON values (array or object) from a JSON object or array.
json_extract_scalar	json_extract_scalar(x, json_path)	Extracts a set of scalar values (strings, integers, or Boolean values) from a JSON object or array. Similar to the `json_extract` function.
json_format	json_format(x)	Converts a JSON value into a string value.
json_parse	json_parse(x)	Converts a string value into a JSON value.
json_size	json_size(x, json_path)	Calculates the number of elements in a JSON object or array.

Parameter	Description
x	The parameter value is a JSON array.
value	Value.

Parameter	Description
x	The parameter value is a JSON object or array.
json_path	JSONPath, such as `$.store.book[0].title`. Note: JSON syntax requiring array element traversal is not supported, such as the following: `$.store.book[*].author`, `$..book[(@.length-1)]`, `$..book[?(@.price<10)]`.

tencent cloud

가입하기

로그인

Recent Pages

JSON Functions

json_array_contains

Syntax

Parameter description

Return value type

Example

json_array_get

Syntax

Parameter description

Return value type

Example

json_array_length

Syntax

Parameter description

Return value type

Example

json_extract

Syntax

Parameter description

Return value type

Example

json_extract_scalar

Syntax

Parameter description

Return value type

Example

json_format

Syntax

Parameter description

Return value type

Example

json_parse

Syntax

Parameter description

Return value type

Example

json_size

Syntax

Parameter description

Return value type

Example

문제 해결에 도움이 되었나요?

문제 해결에 도움이 되었나요?