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?