Postgresql – How to output string values in single quotes

postgresqlstring

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 as json and cast it to text afterwards:

SELECT ('{"a":[1,2,3],"b":["4","5","6"]}'::json#>'{b,2}')::text;

 text 
------
 "6"
(1 row)

There is also the json_typeof function that can tell you what JSON type the value has:

SELECT json_typeof('{"a":[1,2,3],"b":["4","5","6"]}'::json#>'{b,2}');

 json_typeof 
-------------
 string
(1 row)