How to keep array fields in PostgreSQL jsonb

Question

It seems that PostgreSQL (12+) cannot keep the PostgreSQL array type in jsonb objects. If a PostgreSQL array is stored in a jsonb, it seems that it's converted to a jsonb array.

For example, given the following array:

=> SELECT ARRAY['world'];
  array  
---------
 {world}
(1 row)

If one stores it into a jsonb field and then extract that field as a string:

=> SELECT (jsonb_build_object('location', ARRAY['world'])::text::jsonb)->>'location';
 ?column?  
-----------
 ["world"]
(1 row)

, the output isn't a PostgreSQL array (or string representation of an array) anymore.

The array's type seems to have been erased when it's stored into the jsonb. So one cannot use the array as it is anymore.

For example, the format_x() function/extension works correctly on simple types:

=> SELECT format_x('Hello %(location)s!', jsonb_build_object('location', 'world'));
   format_x   
--------------
 Hello world!

, but breaks down (produces incorrect result) on array types.

=> SELECT format_x('Hello %(location)L!', jsonb_build_object('location', ARRAY['world']));
      format_x      
--------------------
 Hello '["world"]'!

In above, the array is no longer represented as an PostgreSQL array, which breaks things if you use this to build a dynamic SQL.

My question is:

Is there a way to store PostgreSQL arrays faithfully in JSONB objects?

Answer 1

Convert the array to a text representation before storing it. It looks like you tried that, but you put the cast in the wrong place.

SELECT (jsonb_build_object('location', ARRAY['world']::text))->>'location';

But you might need to cast back to text[] as it won't remember what it used to be:

SELECT ((jsonb_build_object('location', ARRAY['world']::text))->>'location')::text[];