2

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.

2
  • Why do you need quoted values?
    – McNets
    Oct 13, 2019 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, 2019 at 2:10

1 Answer 1

2

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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