Extract int, string, boolean etc. As the appropriate PostgreSQL type from JSON

Question

I feel like I just need to skip something simple, but I went through the PostgreSQL documentation on JSON and JSON operators and functions and see nothing to explain.

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 return me a good result, full json s:

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

But how to convert these json values ​​back to their original types? I do not expect that I can do this in one result set, of course, since the types are incompatible. I just mean individually.

Lots of things I've tried

Casting sure doesn't work:

 SELECT to_json(5)::NUMERIC; 

gives

 ERROR: cannot cast type json to numeric 

If I try to use the json_populate_record function as follows:

 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 specify a type: SELECT json_typeof(to_json(5)); gives number , but that doesn't help me extract it.

Also json_to_record (also 9.4):

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

gets another error:

 ERROR: cannot call json_to_record on a scalar 

So, how do you convert json "scalars" (since PG calls them apparently) to the corresponding type of PG?

I am interested in 9.3 and 9.4; 9.2 will be just a bonus.

Answer 1

The easiest way for booleans and numbers, apparently, is to first apply to TEXT and then apply 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 the lines where instead you return the quoted value trying:

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

Apparently, this part of my question has already been considered . You can get around this 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 in structures (usually this is the case), you need to use the correct operators / functions to retrieve your data string representation: ->> (9.3+), #>> (9.3 +), json_each_text() (9.3+), json_array_elements_text() (9.4 +).

To select the textual representation of the json elements of the array 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 being processed (json nulls converted to sql NULL using these methods). To select numbers , you need to use numeric casts (this is completely ¹ compatible with json number). To select booleans , use casts to boolean (both true and false supported as input representations). But keep in mind that dropping can cause your request to fail if their input representation is not accepted. F.ex. if you have a json object in some of your columns, this object usually has some key, which is usually a number (but not always), this request may fail:

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

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

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

¹ I did not check their complete syntaxes, but numeric also accepts scientific notation, so theoretically all json numbers should be processed correctly.

For 9.2+, this function can check the json value 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$; 

Answer 3

This is a question similar to yours. In fact, the basic representations of data types at the bit level are incompatible and converting a scalar to a native type is not what was implemented due to ambiguities. JSON has a very strict specification that is closely related to javascript objects and natives.

Maybe, but I don’t think it was implemented.