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

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.

Answer 2

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

Result:

col
---
one
two

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