21.7. Retrieving the JSON Array Length (json_array_length) #
Required privileges: Postgres Pro AXE administrator only. However, all roles can execute this stored procedure on data of the pg_catalog.json type. For a full list of stored procedures and privileges, refer to Section 12.1.
Execute the following command on the Postgres Pro AXE server:
SELECT json_array_length(JSON[,path]) ON CONFLICT DO NOTHING;
Where:
JSON: The JSON data whose array length must be retrieved.You must specify a JSON string literal or a column with JSON data.
path: The path to the JSON element whose array length must be retrieved.You can use JSONPath (e.g.,
$.family) or JSON Pointer (e.g.,/family). A list of paths can also be specified, in which case a list of array lengths is returned.Optional parameter.
Postgres Pro AXE returns the array length of the JSON data or 0 if the JSON data is not a JSON array.
Example 21.7. Executing the json_array_length Stored Procedure
JSON data in the j column of the example table:
{
"family": "anatidae",
"species": ["duck", "goose", "swan", null]
}
Retrieving the JSON array length:
SELECT json_array_length(j, '$.species') FROM example;
4