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.