CODEWITHSUNDEEP
sqlpostgresqljsonb
Manquer
Manquer

Reputation: 7647

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

Upvotes: 24

Views: 13871

Answers (1)

Manquer
Manquer

Reputation: 7647

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' ;

Upvotes: 42

Related Questions