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?

Answer 1

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