3

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?

4

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.

  • Thank you for your feedback, but I actually meant an SQL array of JSON documents, not a JSON array of JSON objects. So it is indeed a json[]. Consider something like SELECT ARRAY(SELECT json_build_object('hello', 'world')). – Jarius Hebzo Aug 2 '18 at 7:05
  • You would just treat it like any other array if you want to know if it's empty just do select thing = '{}'::json[];, or test for array_length( '{}'::json[], 1 ) = 0 – Evan Carroll Aug 2 '18 at 7:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.