JSON_LENGTH Function Syntax, Parameters & Return Values - MaxCompute - Alibaba Cloud - MaxCompute

The JSON_LENGTH function returns the length of JSON data at a specified path.

Syntax

BIGINT JSON_LENGTH(JSON <json> [, STRING <json_path>])

-- Standard example.
-- Returns 2.
SELECT JSON_LENGTH(JSON '{"k1":"v31","k2":300}');

Parameters

json: Required. The JSON data. The data type is JSON.
json_path: Optional. The path to the JSON data. This parameter is of the STRING data type. If you do not specify this parameter, the function returns the length of the entire JSON data by default.

Return value

Returns a BIGINT value based on the following rules:

If json is a JSON array, the function returns the number of elements in the array.
If json is a JSON object, the function returns the number of members in the object.
If the json data is not a JSON array or a JSON object, the function returns 1.
If json or json_path is null, the function returns null.
This function does not count the elements or members within nested arrays or objects.

Examples

Example 1: Basic example

-- Returns 2.
SELECT JSON_LENGTH(JSON '{"k1":"v31","k2":300}');

-- Returns 6.
SELECT JSON_LENGTH(JSON '[1,2,3,4,5,6]');

-- Returns 3. The length of nested arrays or objects is not considered.
SELECT 
  JSON_LENGTH(
    JSON '{"name": "John", "age": 22, "skills":["PHP","MySQL","JavaScript"]}'
  );

-- Returns 2.
SELECT JSON_LENGTH(JSON '{"x": 1, "y": [1, 2]}', '$.y');

-- Returns null.
SELECT JSON_LENGTH(null);

Example 2: The function returns 1 if the json data is not a JSON array or a JSON object.

-- Returns 1.
SELECT JSON_LENGTH(JSON 'null');

-- Returns 1.
SELECT JSON_LENGTH(JSON '123');

-- Returns 1.
SELECT JSON_LENGTH(JSON '123.34');

-- Returns 1.
SELECT JSON_LENGTH(JSON 'true');

Related functions

JSON_LENGTH is a JSON function. For more information about other JSON functions, see JSON functions.