SQL JSON Functions

Updated on 2025-02-12 GMT+08:00

View PDF

JSON functions are used to parse JSON objects or JSON arrays and extract values from them.

SELECT json_extract(Results, '$.[0].EndTime')

**Table 1** JSON function statements
Statement	Description	Example	Returned Value Type
json_extract	Extracts a set of JSON values (object or array) from a JSON object or JSON array.	json_extract(x, json_path)	Data of string type in JSON format
json_extract_scalar	Extracts a set of scalar values (string, integer, or Boolean) from a JSON object or JSON array. If the specified JSON path is not a scalar, null is returned.	json_extract_scalar(x,json_path)	varchar type

json_extract function

Obtain the value of the EndTime field in the Results field.

Example field

Results:[{"EndTime":1626314520},{"FireResult":2}]

Query and analysis statement

SELECT json_extract(Results, '$.[0].EndTime')

Query and analysis result

**Table 2** Query and analysis results
EXPR$0
1626314520

json_extract_scalar function

Obtain the values of the RawResultCount field from the Results field, convert these values to bigint type, and sum them up.

Example field

Results:[{"EndTime":1626314520},{"RawResultCount":1}]

Query and analysis statement

SELECT sum(cast(json_extract_scalar(Results,'$.[1].RawResultCount') AS bigint) )

Query and analysis result

**Table 3** Query and analysis result
EXPR$0
1546

Parent topic: SQL Analysis Syntax

Thank you very much for your feedback. We will continue working to improve the documentation.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel