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}';


(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}';
(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 --


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.

  • Why do you need quoted values? – McNets Oct 13 '19 at 18:41
  • Operator #>> will output text type. That is the reason 6 is not quoted. You can use to_json or json_each/jsonb_each to keep the quote. – binhgreat Oct 14 '19 at 2:10

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;

(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}');

(1 row)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.