On this page

Show all

Help Center/ MapReduce Service/ Component Operation Guide (LTS)/ Using HetuEngine/ Common HetuEngine SQL Syntax/ HetuEngine SQL Functions and Operators/ JSON Functions and Operators

JSON Functions and Operators

Updated on 2024-12-13 GMT+08:00

View PDF

Cast to JSON

SELECT CAST(9223372036854775807 AS JSON); -- JSON '9223372036854775807'

Cast from JSON

SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]

JSON Function

NOTE:

The conversion from NULL to JSON cannot be simply implemented. Converting from a separate NULL produces a SQLNULL instead of JSON'null'. However, when converted from an array or Map containing NULL, the generated JSON will contain NULL.

When converted from ROW to JSON, the result is a JSON array, not a JSON object. This is because for rows in SQL, the location is more important than the name.

The value can be converted from BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, REAL, DOUBLE, or VARCHAR. If the element type of an array is one of the supported types, the key type of a map is VARCHAR, and the value type of a map is one of the supported types, or the type of each field in a row is one of the supported types, the array can be converted from ARRAY, MAP, or ROW. The following example shows the behavior of the transformation:

SELECT CAST(NULL AS JSON);-- NULL
SELECT CAST(1 AS JSON);-- JSON '1'
SELECT CAST(9223372036854775807 AS JSON);-- JSON '9223372036854775807'
SELECT CAST('abc' AS JSON);-- JSON '"abc"'
SELECT CAST(true AS JSON);-- JSON 'true'
SELECT CAST(1.234 AS JSON);-- JSON '1.234'
SELECT CAST(ARRAY[1, 23, 456] AS JSON);-- JSON '[1,23,456]'
SELECT CAST(ARRAY[1, NULL, 456] AS JSON);-- JSON '[1,null,456]'
SELECT CAST(ARRAY[ARRAY[1, 23], ARRAY[456]] AS JSON);-- JSON '[[1,23],[456]]'
SELECT CAST(MAP(ARRAY['k1', 'k2', 'k3'], ARRAY[1, 23, 456]) AS JSON);-- JSON '{"k1":1,"k2":23,"k3":456}'
SELECT CAST(CAST(ROW(123, 'abc', true) AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)) AS JSON);-- JSON '[123,"abc",true]'

JSON-to-other Types

SELECT CAST(JSON 'null' AS VARCHAR);-- NULL  
SELECT CAST(JSON '1' AS INTEGER);-- 1  
SELECT CAST(JSON '9223372036854775807' AS BIGINT);-- 9223372036854775807  
SELECT CAST(JSON '"abc"' AS VARCHAR);-- abc  
SELECT CAST(JSON 'true' AS BOOLEAN);-- true  
SELECT CAST(JSON '1.234' AS DOUBLE);-- 1.234  
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER));-- [1, 23, 456]  
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER));-- [1, NULL, 456]  
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER)));-- [[1, 23], [456]]  
SELECT CAST(JSON '{"k1":1, "k2":23, "k3":456}' AS MAP(VARCHAR, INTEGER));-- {k1=1, k2=23, k3=456}  
SELECT CAST(JSON '{"v1":123, "v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN));-- {v1=123, v2=abc, v3=true}  
SELECT CAST(JSON '[123, "abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN));-- {value1=123, value2=abc, value3=true}  
SELECT CAST(JSON'[[1, 23], 456]'AS ARRAY(JSON));-- [JSON '[1,23]', JSON '456']  
SELECT CAST(JSON'{"k1": [1, 23], "k2": 456}'AS MAP(VARCHAR,JSON));-- {k1 = JSON '[1,23]', k2 = JSON '456'}  
SELECT CAST(JSON'[null]'AS ARRAY(JSON));-- [JSON 'null']

NOTE:

JSON arrays and JSON objects are supported during conversion from JSON to ROW.

JSON arrays can have mixed element types, and JSON Maps can have mixed value types. This makes it impossible to convert it to SQL arrays and maps in some cases. To solve this problem, HetuEngine supports partial conversion of arrays and maps.

SELECT CAST(JSON'[[1, 23], 456]'AS ARRAY(JSON));-- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON'{"k1": [1, 23], "k2": 456}'AS MAP(VARCHAR,JSON));-- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON'[null]'AS ARRAY(JSON));-- [JSON 'null']

is_json_scalar(json) → boolean
Check whether JSON is a scalar (that is, JSON number, JSON character string, true, false, or null).
```
select is_json_scalar(json'[1,22]'); -- false
```
json_array_contains(json, value) → boolean
Checks whether a value is contained in json.
```
select json_array_contains(json '[1,23,44]',23); -- true
```
json_array_get(json_array, index) → json
NOTICE:

The semantics of the function has been broken. If the extracted element is a string, it will be converted to an invalid JSON value that is not correctly enclosed in quotation marks (the value will not be enclosed in quotation marks, and any internal quotation marks will not be escaped). You are not advised to use this function. The function cannot be corrected without affecting existing usage and may be deleted in future versions.

Returns the JSON element at the specified index position. The index starts from 0.
```
SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'
```
The index page supports negative numbers, indicating that the index page starts from the last element. The value -1 indicates the last element. If the index length exceeds the actual length, null is returned.
```
SELECT json_array_get('["c", [3, 9], "a"]', -1); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["c", [3, 9], "a"]', -2); -- JSON '[3,9]'
```
If the JSON element at the specified index does not exist, NULL is returned.
```
SELECT json_array_get('[]', 0);                -- NULL
SELECT json_array_get('["a", "b", "c"]', 10);  -- NULL
SELECT json_array_get('["c", "b", "a"]', -10); -- NULL
```

json_array_length(json) → bigint

Returns the length of json.

SELECT json_array_length(json '[1,2,3,4]'); -- 4
SELECT json_array_length('[1, 2, 3]'); -- 3

get_json_object(string json,string json_path);
Captures information in json based on the json_path format.
```
SELECT get_json_object('{"id": 1, "value":"xxx"}', '$.value');  -- "xxx"
```
json_extract(json, json_path) → json
Captures information in json based on the json_path format.
```
SELECT json_extract(json '{"id": 1, "value":"xxx"}', '$.value');-- JSON "xxx" 
```
json_extract_scalar(json, json_path) → varchar
The function is the same as that of json_extract. The return value is varchar.
```
SELECT json_extract_scalar(json '{"id": 1, "value": "xxx"}', '$.value'); -- xxx
```

json_format(json) → varchar

Converts a JSON value to a serialized JSON text. This is the inverse function of json_parse.

SELECT JSON_format(json '{"id": 1, "value":"xxx"}'); -- {"id":1, "value":"xxx"}

Notes:

json_format and CAST(json AS VARCHAR) have completely different semantics.

json_format serializes the input JSON value into JSON text that complies with the 7159 standard. The JSON value can be a JSON object, JSON array, JSON string, JSON number, true, false, or null:

SELECT json_format(JSON '{"a": 1, "b": 2}'); -- '{"a":1,"b":2}' 
SELECT json_format(JSON '[1, 2, 3]'); -- '[1,2,3]' 
SELECT json_format(JSON '"abc"'); -- '"abc"' 
SELECT json_format(JSON '42'); -- '42' 
SELECT json_format(JSON 'true'); -- 'true' 
SELECT json_format(JSON 'null'); -- 'null'

CAST(json AS VARCHAR) converts the JSON value to the corresponding SQL VARCHAR value. For JSON strings, JSON numbers, true, false, or null, the conversion behavior is the same as that of the corresponding SQL type. JSON objects and JSON arrays cannot be converted to VARCHAR.

SELECT CAST(JSON '{"a": 1, "b": 2}' AS VARCHAR); -- NULL 
SELECT CAST(JSON '[1, 2, 3]' AS VARCHAR); -- NULL 
SELECT CAST(JSON '"abc"' AS VARCHAR); -- 'abc'; Note the double quote is gone 
SELECT CAST(JSON '42' AS VARCHAR); -- '42' 
SELECT CAST(JSON 'true' AS VARCHAR); -- 'true' 
SELECT CAST(JSON 'null' AS VARCHAR); -- NULL

json_parse(string) → json
Contrary to json_format(json), convert a JSON character string to JSON.

json_parse and json_extract are used together to parse JSON character strings in data tables.
```
select JSON_parse('{"id": 1, "value":"xxx"}'); -- json {"id":1, "value":"xxx"}
```

json_size(json, json_path) → bigint

It is similar to json_extract, but the number of objects in JSON is returned.

SELECT json_size('{ "x": {"a": 1, "b": 2} }', '$.x'); => 2
SELECT json_size('{ "x": [1, 2, 3] }', '$.x'); =>3
SELECT json_size('{ "x": {"a": 1, "b": 2} }', '$.x.a'); => 0

Parent topic: HetuEngine SQL Functions and Operators

Previous topic: Binary Functions and Operators

Next topic: Date and Time Functions and Operators

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

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

For any further questions, feel free to contact us through the chatbot.

Chatbot