JSON_LENGTH()¶

JSON_LENGTH() returns the length of a JSON document. For a JSON object, the length is the number of members. For a JSON array, the length is the number of elements. For scalar JSON values, the length is 1. An optional path argument can target a nested value.

Description¶

JSON_LENGTH() counts elements in a JSON document:

A JSON object: returns the number of top-level key-value pairs.
A JSON array: returns the number of elements.
A JSON scalar ("string", 42, true, false, null): returns 1.
A non-existent path: returns NULL.
A NULL argument: returns NULL.

When a path argument is provided, the function evaluates the path and returns the length of the value found at that location. Scalar JSON values at the path return 1.

Syntax¶

> JSON_LENGTH(json_doc [, path])

Arguments¶

Arguments	Description
json_doc	Required. A JSON document (string, JSON column, or result of a JSON function).
path	Optional. A JSON path expression specifying a nested value whose length to measure.

Examples¶

DROP DATABASE IF EXISTS dbfuncs;
CREATE DATABASE dbfuncs;
USE dbfuncs;

-- NULL input returns NULL
SELECT JSON_LENGTH(NULL);

-- Object: number of key-value pairs
SELECT JSON_LENGTH('{}');
SELECT JSON_LENGTH('{"a":1,"b":2}');

-- Array: number of elements
SELECT JSON_LENGTH('[]');
SELECT JSON_LENGTH('[1,2,3]');

-- Scalar values all return 1
SELECT JSON_LENGTH('true');
SELECT JSON_LENGTH('false');
SELECT JSON_LENGTH('null');
SELECT JSON_LENGTH('"hello"');
SELECT JSON_LENGTH('42');

-- Nested path
SELECT JSON_LENGTH('{"a":{"b":[1,2,3]}}', '$.a.b');

-- Path to non-existent member
SELECT JSON_LENGTH('{"a":1}', '$.x');

-- Table with JSON column
CREATE TABLE t_json_len (id INT PRIMARY KEY, payload JSON);
INSERT INTO t_json_len VALUES (1, '{"meta":{"level":{"nested":{"value":{"deep":"2924"}},"flag":true}}}');
SELECT id, JSON_LENGTH(payload) FROM t_json_len;

DROP TABLE t_json_len;
DROP DATABASE dbfuncs;