Unquoting JSON strings; print JSON strings without quotes

Question

SELECT json_array_elements('["one", "two"]'::json)

gives result

| json_array_elements |
| :------------------ |
| "one" |
| "two" |

I would like to have the same but without the quotes:

one
two

Looks like I can't use ->> here because I don't have field names in the JSON. It's just an array of strings.

Postgres version: PostgreSQL 10.0 on x86_64-apple-darwin, compiled by i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 23361100), 64-bit

• Vérace: What version of PostgreSQL?
• McNets: This question have an answer here: dba.stackexchange.com/a/57121/110455
• Vérace: @McNets - what about this?
• McNets: @Vérace on db-fiddle shows results without quotes but dbfiddle.uk shows results with quotes. I can't check it on my postgres VM (I don't remember the password) but I suppose it will show the quotes.
• McNets: @Vérace psql shows it with quotes too

Answer 1

The default json->text coercion outputs with a double quote (") because coercing from text to a json string requires you to double-quote your input. To get rid of the double-quotes, use TRIM

SELECT x, trim('"' FROM x::text)
FROM json_array_elements('["one", "two"]'::json) AS t(x);
 x | btrim 
-------+-------
 "one" | one
 "two" | two
(2 rows)

Important point though, you lose some utility if you do that. All JSONB types get returned in a textual form that can be used to go back to jsonb with the text->jsonb coercion. It's a bijective mapping function. Losing that means null and "null" are the same, as are 1 and "1".

SELECT x, trim('"' FROM x::text)
FROM json_array_elements('[null, "null", 1, "1"]') AS t(x);
 x | btrim 
--------+-------
 null | null
 "null" | null
 1 | 1
 "1" | 1
(4 rows)

Internals..

If you want to know what's happening. All types can provide an _out which takes them to text or _send which takes them to binary representation and a reciprocal _in and _recv which takes them from those forms and maps back to the types. Here you're getting jsonb_out,

1. jsonb_out which calls JsonbToCstring
2. JsonbToCstring which calls JsonbToCStringWorker
3. JsonbToCStringWorker which calls jsonb_put_escaped_value
4. jsonb_put_escaped_value(StringInfo out, JsonbValue *scalarVal) which calls escape_json
5. escape_json(StringInfo buf, const char *str) which adds the " and it's hardcoded. No other way.

• psmith: Note that btrim gives incorrect result if the string contains double quotes while @slava-v's answer below is better: SELECT x, trim('"' FROM x::text), x#>>'{}' FROM json_array_elements('[null, "nu\"ll", 1, "1"]') AS t(x);

Answer 2

SELECT value#>>'{}' as col FROM json_array_elements('["one", "two"]'::json);

Result:

col
---
one
two

• Dawn Drescher: As a bit of an explanation: The operators that contain >> seem to generally convert a JSON to unescaped text: postgresql.org/docs/current/functions-json.html. This answer is probably preferable since the escaping is probably not limited to adding quotation marks, but also escapes quotation marks in the text and some special characters. The samples above just so happen not to contain any. ("Probably" because I didn’t test it.)
• jso: Rather the #>> is a JSON operator to return an object at a path, see: postgresql.org/docs/10/functions-json.html
• Sarke: "How do you unquote a JSON string in postgres?" "Oh it's easy, just add #>>'{}' after. Should be obvious." 😂 Take your upvote, wizard!

Answer 3

SELECT json_array_elements_text('["one", "two"]'::json)

of if using jsonb instead:

SELECT jsonb_array_elements_text('["one", "two"]'::jsonb)

Answer 4

You could use jsonb_array_elements instead:

SELECT jsonb_array_elements('["one", "two"]'::jsonb) ->> 0

Result:

col
---
one
two