Search code examples
postgresqlplpgsql

PLPGSQL: Query JSONB Array size

How do I query array size of a JSONB object?

DO 
$$

DECLARE 
    p_obj JSONB;
    array_size INT;
BEGIN
    p_obj = '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::jsonb;  -- This prints out NOTICE:  [{"a": "foo"}, {"b": "bar"}, {"c": "baz"}]
    array_size = json_array_length(p_obj);

RAISE NOTICE '%', p_obj;
RAISE NOTICE '%', array_size;

END;
$$ LANGUAGE PLPGSQL;

This results in:

ERROR:  function json_array_length(jsonb) does not exist
LINE 1: SELECT json_array_length(p_obj)
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT json_array_length(p_obj)
CONTEXT:  PL/pgSQL function inline_code_block line 7 at assignment 

SQL state: 42883

How can I get the size of array so I can use it in a FOR LOOP?

Solution

  • To get length of a jsonb array, then you will need to use jsonb_array_length.

    json_array_length is for json only.