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 other than JSON null, the length is 1; JSON null returns SQL
NULL. 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): returns 1. JSONnullreturns SQLNULL.A non-existent path: returns
NULL.A
NULLargument: returnsNULL.
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 other than JSON null at the path return 1; JSON null returns SQL NULL.
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 other than JSON null return 1
SELECT JSON_LENGTH('true');
SELECT JSON_LENGTH('false');
-- JSON null returns NULL
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;