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
(to_json(5)),
(to_json(true)),
(to_json('foo'::TEXT))
) x (j);


will give me back a nice result set full of
json
s:

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

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.