jpmc26 jpmc26 - 1 month ago 12
JSON Question

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

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
) x (j);

will give me back a nice result set full of

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

But how do I convert these
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;


ERROR: cannot cast type json to numeric

If I try to abuse the
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));
, but that doesn't help me actually extract it.

Neither does
(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
"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.


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

FROM (VALUES ('5.4575e6'::json)) x (j)
-- Result is 5457500, with column type NUMERIC
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:

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.