How to safely find `jsonb_array_length` in Postgres if argument can be null::jsonb

I have a table with jsonb column in Postgres:

create table mytable(items jsonb not null);

items column normally is a json array, but also can hold null scalar value.

I need to find jsonb_array_length(items), which should be 0 if there is null value inside, but not cannot get array length of a scalar error.

UPD:

I’ve got it working with following ugly code:

select jsonb_array_length(jsonb_path_query_array(jsonb_build_array(items, '[]'::jsonb), '$[*] ? (@ != null)'));

but looks like more simple solution may exist.

>Solution :

You can use the jsonb_typeof function to distinguish arrays from other json values (such as null):

SELECT CASE jsonb_typeof(items) WHEN 'array' THEN jsonb_array_length(items) ELSE 0 END
FROM mytable;

or also

SELECT CASE jsonb_typeof(items)
  WHEN 'array' THEN jsonb_array_length(items)
  WHEN 'null' THEN 0
END
FROM mytable;

which will produce (SQL) NULL when items is neither an array nor null value.