7
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

  • What version of PostgreSQL? – Vérace May 27 '18 at 18:37
  • This question have an answer here: dba.stackexchange.com/a/57121/110455 – McNets May 27 '18 at 18:37
  • @McNets - what about this? – Vérace May 27 '18 at 18:42
  • @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 May 27 '18 at 19:32
  • 2
    @Vérace psql shows it with quotes too – McNets May 27 '18 at 19:37
5

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.
6
SELECT value#>>'{}' as col FROM json_array_elements('["one", "two"]'::json);

Result:

col
---
one
two
  • 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.) – Denis Drescher Jul 4 at 15:12
  • Rather the #>> is a JSON operator to return an object at a path, see: postgresql.org/docs/10/functions-json.html – jso Aug 29 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.