Skip to main content

JSON Functions

Extreme DPS provides a series of JSON functions to help you process JSON data.

JSON_ARRAY_CONTAINS​

Checks whether a JSON array in a string contains a specified value.

Syntax​

JSON_ARRAY_CONTAINS(<json_array>, <value>)

Arguments​

  • <json_array>

    The varchar string that contains a JSON array.

  • <value>

    The specified value, whose data type can be varchar, double precision, bigint, or boolean.

Returns​

A boolean value.

Examples​

SELECT JSON_ARRAY_CONTAINS('[1, -1.1, true]', 1); 
-> true
SELECT JSON_ARRAY_CONTAINS('[-2, 2.2, false]', 1); 
-> false
SELECT JSON_ARRAY_CONTAINS('[1, -1.1, true]', -1.1); 
-> true
SELECT JSON_ARRAY_CONTAINS('[1, -1.1, true]', true); 
-> true
SELECT JSON_ARRAY_CONTAINS('["a", null]', 'a'); 
-> true

JSON_ARRAY_LENGTH​

Returns the length of a JSON array contained in a string.

Syntax​

JSON_ARRAY_LENGTH(<json_array>)

Arguments​

<json_array>: the varchar string that contains a JSON array.

Returns​

A bigint value.

Example​

SELECT JSON_ARRAY_LENGTH('[1, -1.1, true]'); 
-> 3

JSON_EXTRACT_SCALAR​

Extracts a scalar value at a specified path from a JSON string and returns that value as a plain string.

Syntax​

JSON_EXTRACT_SCALAR(<json>, <json_path>)

Arguments​

  • <json>

    The varchar JSON string.

  • <json_path>

    The varchar JSON path string that specifies the element to extract.

Returns​

The varchar string.

Examples​

SELECT JSON_EXTRACT_SCALAR('{"a": 1, "b": 2}', '$.a');
-> 1
SELECT JSON_EXTRACT_SCALAR('["aa", "bb", "cc"]', '$[1]');  
-> bb
SELECT JSON_EXTRACT_SCALAR('{"a": {"b": 1}, "c": "xxx"}', '$.a'); 
-> NULL

JSON_SIZE​

Returns the size of the value at a path specified by json_path in a JSON string.

Syntax​

JSON_SIZE(<json>, <json_path>)

Arguments​

  • <json>

    The varchar JSON string.

  • <json_path>

    The varchar JSON path string.

Returns​

A bigint value.

Examples​

SELECT JSON_SIZE('{"dict": {"a": 1, "b": 2}, "array": [1, [], 3]}', '$.dict'); 
-> 2
SELECT JSON_SIZE('{"dict": {"a": 1, "b": 2}, "array": [1, [], 3]}', '$.array'); 
-> 3