Unquoting JSON strings; print JSON strings without quotes

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 2336.11.00), 64-bit

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,

• jsonb_out which calls JsonbToCstring

• JsonbToCstring which calls JsonbToCStringWorker

• JsonbToCStringWorker which calls jsonb_put_escaped_value

• jsonb_put_escaped_value(StringInfo out, JsonbValue *scalarVal) which calls escape_json

• escape_json(StringInfo buf, const char *str) which adds the " and it's hardcoded. No other way.

StackExchange Database Administrators Q#207984, answer score: 19