I'm trying to convert values stored in a json into its string representation.
The #>>
operator works for this purpose for numbers, e.g.:
SELECT '{"a":[1,2,3],"b":["4","5","6"]}'::json#>>'{a,2}';
gives
?column?
------
3
(1 row)
But for string values, the text representation (6
in the example below) does not have single quotes around it, so that one cannot tell that the stored value was a string:
SELECT '{"a":[1,2,3],"b":["4","5","6"]}'::json#>>'{b,2}';
?column?
----------
6
(1 row)
How to use a function or expression to let PostgreSQL output '6'
instead of 6
in the second example above (and output 3
in the first example)?
(This is with PostgreSQL 12)
-- UPDATE --
@McNets
To clarify, I am interested in getting the string representation in quotes, so that after decomposing a jsonb into its key value pairs, one can use them with inverse functions like jsonb_build_object
(so to speak) to reconstruct the original jsonb.
jsonb_each_text
and the #>>
apparently aren't good enough because they drop the single quotes.
#>>
will output text type. That is the reason6
is not quoted. You can useto_json
orjson_each
/jsonb_each
to keep the quote.