Sample Header Ad - 728x90

How to output string values in single quotes?

2 votes
1 answer
2095 views
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.
Asked by tinlyx (3820 rep)
Oct 13, 2019, 06:31 PM
Last activity: Mar 5, 2021, 09:07 PM