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.
Best Answer
Rather than retrieving the value as
text
with#>>
, use#>
to retrieve it asjson
and cast it totext
afterwards:There is also the
json_typeof
function that can tell you what JSON type the value has: