What is the fastest way to verify that a json[] array is not empty?

Question

I want to verify that an json[] column the_array is not empty. For a normal array, I could check on:

the_array != '{}'

However, that does not work for a json[], as demonstrated below:

select '{}'::json[] != '{}'
ERROR:  could not identify an equality operator for type json

I could use the following check instead:

array_length(the_array, 1) != null // yes, array_length on an
                                      empty array returns null

My fear is that array_length() would iterate over the whole array to count the number of items and then return that count. In my case, I don't need the actual size of the array, I just need to know whether it is empty or not.

So, does array_length() iterate over the whole array? And if so, is there a better way to check if a json array is empty?

Answer 1

This is not returning a JSON Array,.

SELECT '{}'::json[]

The type json[] is not a "JSON Array", it's a SQL Array of type JSON. You should never be using this kind of structure. It's almost certainly a red flag of bad design.

What you want to check is that a Real JSON Array is not empty, you can do that with jsonb_array_length(jsonb). JSON is stored as JSON, or JSONB for binary json.

SELECT jsonb_array_length( '[]'::jsonb );

To see if it's empty,

SELECT jsonb_array_length( '[]'::jsonb ) = 0;

Warning, this just counts those elements, they may be null,

SELECT jsonb_array_length( '[null]'::jsonb );

So you may want to call jsonb_strip_nulls() on the array first.