In PostgreSQL, how can I unwrap a json string to text?

Question

Suppose I have a value of type json, say y. One may obtain such a value through, for example, obj->'key', or any function that returns values of type json.

This value, when cast to text, includes quotation marks i.e. "y" instead of y. In cases where using json types is unavoidable, this poses a problem, especially when we wish to compare the value with literal strings e.g.

select foo(x)='bar';

The API Brainstorm page suggests a from_json function that will intelligently unwrap JSON strings, but I doubt that is available yet. In the meantime, how can one convert JSON strings to text without the quotation marks?

Does this answer your question? [Postgres: How to convert a json string to text?](https://stackoverflow.com/questions/27215216/postgres-how-to-convert-a-json-string-to-text) — Bergi, Jul 07 '22 at 00:09

ADJenks · Accepted Answer · 2020-05-19T19:42:33.063

Text:

To extract a value as text, use #>>:

SELECT to_json('foo'::text) #>> '{}';

From: Postgres: How to convert a json string to text?

PostgreSQL doc page: https://www.postgresql.org/docs/11/functions-json.html

So it addresses your question specifically, but it doesn't work with any other types, like integer or float for example. The #> operator will not work for other types either.

Numbers:

Because JSON only has one numeric type, "number", and has no concept of int or float, there's no obvious way to cast a JSON type to a "correct" numeric type. It's best to know the schema of your JSON, extract the text and then cast to the correct type:

SELECT (('{"a":2.01}'::json)->'a'#>>'{}')::float

PostgreSQL does however have support for "arbitrary precision numbers" ("up to 131072 digits before the decimal point; up to 16383 digits after the decimal point") with its "numeric" type. JSON also supports 'e' notation for large numbers.

Try this to test them both out:

SELECT (('{"a":2e99999}'::json)->'a'#>>'{}')::numeric

Very good, thanks. Any idea on what to do with ints and floats? — lucid_dreamer, May 19 '20 at 11:34
Oh I see. So `#>>'{}'` _does_ work for other types but always casts to text, thus requiring an additional cast to numeric. PS: You're very helpful. — lucid_dreamer, May 19 '20 at 21:01

score 6 · Answer 2 · answered Jul 30 '15 at 00:04

6

The ->> operator unwraps quotation marks correctly. In order to take advantage of that operator, we wrap up our value inside an array, and then convert that to json.

CREATE OR REPLACE FUNCTION json2text(IN from_json JSON)
RETURNS TEXT AS $$
BEGIN
  RETURN to_json(ARRAY[from_json])->>0;
END; $$
LANGUAGE plpgsql;

For completeness, we provide a CAST that makes use of the function above.

CREATE CAST (json AS text) WITH json2text(json) AS ASSIGNMENT;

answered Jul 30 '15 at 00:04

James Lim

12,915
4
40
65

4

Ugh. There must be a better way. Though I recall this being a real irritation with the 9.3 json API. – Craig Ringer Jul 30 '15 at 01:34
1

@CraigRinger Hopefully someone who knows a better solution sees this! – James Lim Jul 30 '15 at 05:33
There's a better way now - see [the answer below by adjenks](https://stackoverflow.com/posts/54047643/revisions) – lucid_dreamer Nov 10 '20 at 00:39

In PostgreSQL, how can I unwrap a json string to text?

2 Answers2

Text:

Numbers: