Search code examples
sqlpostgresqljsonb

Detect if a jsonb attribute is array or object

Many jsonb/json functions expect all values of the column either to be of type json array ( like jsonb_array_length ) or only an json object (like jsonb_build_oject) .

There are some jsonb columns in the database which contain a mix of both arrays and object roots, is there any easy way to filter out arrays and objects so that queries like 

SELECT DISTINCT jsonb_object_keys(my_column) FROM my_table;
 cannot call jsonb_object_keys on an array

or

SELECT my_column FROM my_table WHERE jsonb_array_length(column) > 0;
cannot get array length of a non-array
Solution

  • As described in documentation the functions jsonb_typeof or json_typeof can be used to apply this kind of filtering

    like 

    SELECT DISTINCT jsonb_object_keys(my_column) 
FROM my_table WHERE jsonb_typeof(column) ='object' ;

    or 

    SELECT my_column FROM my_table 
WHERE jsonb_array_length(column) > 0 
AND jsonb_typeof(column) ='array' ;