SQLite 3 reference · JSON Functions
SQLite JSON_ARRAY_LENGTH() Function
Return the number of elements in a JSON array, optionally at a path. Non-arrays give 0.
SQLite 3 Since 3.9.0
Syntax
json_array_length(json, path)
Parameters
| Parameter | Description |
|---|---|
json |
JSON text (or JSONB). |
path optional |
Where the array is. |
Return value
An integer, or NULL if the path doesn't exist.
Examples
SELECT json_array_length('[1, 2, 3]') AS a,
json_array_length('{"tags":["x","y"]}', '$.tags') AS b,
json_array_length('{"x":1}') AS not_array;
Output
a | b | not_array 3 | 2 | 0
WITH posts(tags) AS (VALUES ('["sql","json"]'), ('[]'))
SELECT tags, json_array_length(tags) AS n FROM posts;
Output
tags | n ["sql","json"] | 2 [] | 0
Version history
| Version | Change |
|---|---|
| SQLite 3.9.0 | Added |
| SQLite 3.38.0 | Built in by default; earlier versions needed the JSON1 extension. |
In other languages
JSONB_ARRAY_LENGTH()· PostgreSQL
Related
JSON_EACH()· A table-valued function that returns one row per element of a JSON array or object, with key, value and type columns.JSON_TYPE()· Return the JSON type of a value or path: 'null', 'true', 'false', 'integer', 'real', 'text', 'array' or 'object'.- SQL String Functions · tutorial