MariaDB JSON_LENGTH(): Arrays, Objects, Paths, and NULL

MariaDB JSON_LENGTH() returns the length of a JSON document or, when a path is supplied, the length of the value at that path.

Syntax

JSON_LENGTH(json_doc)
JSON_LENGTH(json_doc, path)

How Length Is Calculated

MariaDB defines JSON length as follows:

• a scalar has length 1
• an array’s length is the number of top-level elements
• an object’s length is the number of top-level members
• nested arrays and objects are not recursively counted

Array Example

SELECT JSON_LENGTH('[10, 20, 30]');

Result:

3

A nested object still counts as one array element:

SELECT JSON_LENGTH('[1, {"a": 2}, [3, 4]]');

Result:

3

Object Example

SELECT JSON_LENGTH('{"a": 1, "b": 2, "c": 3}');

Result:

3

Nested members are not included in the top-level count:

SELECT JSON_LENGTH('{"a": {"x": 1, "y": 2}}');

Result:

1

Scalar Values

A JSON scalar always has length 1:

SELECT
  JSON_LENGTH('123'),
  JSON_LENGTH('"hello"'),
  JSON_LENGTH('true'),
  JSON_LENGTH('null');

Each expression returns 1.

Length at a JSON Path

Use the optional path argument to measure a nested value:

SELECT JSON_LENGTH(
  '{"users": ["Ada", "Linus", "Grace"]}',
  '$.users'
);

Result:

3

Missing Path

If the path is valid but does not identify a value, MariaDB returns NULL:

SELECT JSON_LENGTH('{"a": 1}', '$.missing');

Result:

NULL

NULL Arguments

If any argument is SQL NULL, the result is NULL:

SELECT
  JSON_LENGTH(NULL),
  JSON_LENGTH('{"a":1}', NULL);

Invalid JSON or Invalid Path

MariaDB raises an error when:

• the JSON document is invalid
• the JSON path is invalid
• the path contains * or ** wildcards

For example, wildcard paths are not accepted by JSON_LENGTH():

SELECT JSON_LENGTH('{"a":[1,2]}', '$.*');

Use a specific path instead.

JSON_LENGTH vs LENGTH

JSON_LENGTH() counts JSON elements or object members.

LENGTH() counts bytes in a string.

They answer different questions:

SELECT
  JSON_LENGTH('[1,2,3]') AS json_items,
  LENGTH('[1,2,3]') AS string_bytes;

Summary

• Arrays return their top-level element count.
• Objects return their top-level member count.
• Scalars return 1.
• Nested values are not recursively counted.
• A missing path returns NULL.
• Invalid JSON, invalid paths, and wildcard paths raise errors.
• JSON_LENGTH() is different from string LENGTH().

See MariaDB’s official JSON_LENGTH documentation.