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);
j | pg_typeof
5 | json
true | json
"foo" | json
ERROR: cannot cast type json to numeric
SELECT json_populate_record(null::INTEGER, to_json(5));
ERROR: first argument of json_populate_record must be a row type
SELECT * FROM json_to_record(to_json(5)) x (i INT);
ERROR: cannot call json_to_record on a scalar
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.