Extract an int, string, boolean, etc. as its corresponding PostgreSQL type from JSON

Question

I feel like I must just be missing something simple here, but I've looked through PostgreSQL's documentation on JSON and the JSON operators and functions and don't see anything explaining it.

It's easy to turn things into JSON in PostgreSQL:

SELECT *, pg_typeof(j) FROM (VALUES
(to_json(5)),
(to_json(true)),
(to_json('foo'::TEXT))
) x (j);

will give me back a nice result set full of jsons:

   j   | pg_typeof
-------+-----------
 5     | json
 true  | json
 "foo" | json

But how do I convert these json values back into their original types? I don't expect to be able to do that all in one result set, of course, since the types aren't consistent. I just mean individually.

Lots of stuff I tried

Casting sure doesn't work:

SELECT to_json(5)::NUMERIC;

gives

ERROR:  cannot cast type json to numeric

If I try to abuse the json_populate_record function like so:

SELECT json_populate_record(null::INTEGER, to_json(5));

I get

ERROR:  first argument of json_populate_record must be a row type

In PG 9.4, I can pretty easily tell the type: SELECT json_typeof(to_json(5)); gives number, but that doesn't help me actually extract it.

Neither does json_to_record (also 9.4):

SELECT * FROM json_to_record(to_json(5)) x (i INT);

gets me another error:

ERROR:  cannot call json_to_record on a scalar

So how do you convert json "scalars" (as PG calls them, apparently) into the corresponding PG type?

I'm interested in 9.3 and 9.4; 9.2 would just be a bonus.

Answer 1

The simplest way for booleans and numbers seems to be to first cast to TEXT and then cast to the appropriate type:

SELECT j::TEXT::NUMERIC
FROM (VALUES ('5.4575e6'::json)) x (j)
-- Result is 5457500, with column type NUMERIC
SELECT j::TEXT::BOOLEAN
FROM (VALUES ('true'::json)) x (j)
-- Result is t, with column type BOOLEAN

This leaves strings, where you instead get back a quoted value trying to this:

SELECT j::TEXT
FROM (VALUES (to_json('foo'::TEXT))) x (j)
-- Result is "foo"

Apparently, that particular part of my question has already been addressed. You can get around it by wrapping the text value in an array and then extracting it:

SELECT array_to_json(array[j])->>0
FROM (VALUES (to_json('foo'::TEXT))) x (j)
-- Result is foo, with column type TEXT.

Answer 2

First step: if your values are contained within structures (which is usually the case), you need to use the correct operators / functions to extract your data's string representation: ->> (9.3+), #>> (9.3+), json_each_text() (9.3+), json_array_elements_text() (9.4+).

To select json array elements' text representation in 9.3, you need something like this:

select json_array ->> indx
from   generate_series(0, json_array_length(json_array) - 1) indx

For scalar values, you can use this little trick:

select ('[' || json_scalar || ']')::json ->> 0 -- ...

At this point, strings and nulls are handled (json nulls convered to sql NULLs by these methods). To select numbers, you need to use casts to numeric (that's fully¹ compatible with json's number). To select booleans, use casts to boolean (both true and false supported as input representations). But note, that casts can make your query fail, if their input representation is not accepted. F.ex. if you have a json object in some of your columns, that object usually have some key, which is usually number (but not always), this query can fail:

select (json_object ->> 'key')::numeric
from   your_table

If you have such data, you need to filter your selects with json_typeof() (9.4+):

select (json_object ->> 'key')::numeric
from   your_table
where  json_typeof(json_object -> 'key') = 'number'

¹ I haven't checked their full syntaxes, but numeric also accepts scientific notation, so in theory, all json numbers should be handled correctly.

For 9.2+, this function can test a json value's type:

create or replace function json_typeof(json)
  returns text
  language sql
  immutable
  strict
as $func$
  select case left(trim(leading E'\x20\x09\x0A\x0D' from $1::text), 1)
    when 'n' then 'null'
    when 't' then 'boolean'
    when 'f' then 'boolean'
    when '"' then 'string'
    when '[' then 'array'
    when '{' then 'object'
    else 'number'
  end
$func$;