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)
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 JsonbToCstringJsonbToCstring which calls JsonbToCStringWorkerJsonbToCStringWorker which calls jsonb_put_escaped_valuejsonb_put_escaped_value(StringInfo out, JsonbValue *scalarVal) which calls escape_jsonescape_json(StringInfo buf, const char *str) which adds the " and it's hardcoded. No other way.
SELECT x, trim('"' FROM x::text), x#>>'{}' FROM json_array_elements('[null, "nu\"ll", 1, "1"]') AS t(x);
SELECT value#>>'{}' as col FROM json_array_elements('["one", "two"]'::json);
Result:
col
---
one
two
>> 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.)
#>> is a JSON operator to return an object at a path, see: postgresql.org/docs/10/functions-json.html
#>>'{}' after. Should be obvious." 😂 Take your upvote, wizard!
SELECT json_array_elements_text('["one", "two"]'::json)
of if using jsonb instead:
SELECT jsonb_array_elements_text('["one", "two"]'::jsonb)
You could use jsonb_array_elements instead:
SELECT jsonb_array_elements('["one", "two"]'::jsonb) ->> 0
Result:
col
---
one
two
