MaxCompute:JSON_LENGTH

How it works

The length of a JSON value is determined as follows:

• Scalar (string, number, boolean, or JSON null): length is always 1. For example, "hello", 123, true, and null all have length 1.

• JSON array: length is the number of elements. For example, [1, 2, 3] has length 3.

• JSON object: length is the number of members. For example, {"a": 1, "b": 2} has length 2.

• Nested arrays or objects: not counted. For example, {"name": "John", "skills": ["SQL", "Python"]} has length 2—the nested array ["SQL", "Python"] is not included.

• Null input: if json or json_path is null, the function returns null.

Syntax

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

Parameters

Return value

Returns a BIGINT value, or null if json or json_path is null.

Examples

-- Object: returns the number of top-level members (2).
SELECT JSON_LENGTH(JSON '{"k1":"v31","k2":300}');

-- Array: returns the number of elements (6).
SELECT JSON_LENGTH(JSON '[1,2,3,4,5,6]');

-- Nested object: nested array is not counted; returns 3.
SELECT JSON_LENGTH(
  JSON '{"name": "John", "age": 22, "skills":["PHP","MySQL","JavaScript"]}'
);

-- Path expression: returns the length of the value at $.y (2).
SELECT JSON_LENGTH(JSON '{"x": 1, "y": [1, 2]}', '$.y');

-- Null input: returns null.
SELECT JSON_LENGTH(null);

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');    -- Returns 1.

Related functions

JSON_LENGTH is a JSON function. For other JSON functions, see JSON functions.