Bread Bread - 9 months ago 23
JSON Question

Cast type given a type name

I have this json structure where i also store a PostgreSQL type name

{
"temperature":{
"value":"(-75,-70]",
"type":"int4range"
}
}


i can get the value such as

SELECT '{"temperature":{"value":"(-75,-70]","type":"int4range"}}'::jsonb #> ARRAY['temperature','value']


now the value type is text and i know that i can only store simple types in json but how can i convert the value to
int4range
dynamically using the type name i have?

Gab Gab
Answer Source

Just as @Bread said you can't dynamically cast data.

However you could do something like:

WITH jsonb as (
    SELECT '{"temperature":{"value":"[-75,-70]","type":"int4range"}}'::json
), fromjsonb as (
    SELECT json->'temperature'->'value' as element,
           json->'temperature'->'type' as type
    FROM jsonb
), trim as (
    SELECT trim(both '"' from element::text) as element,
           trim(both '"' from type::text) as type
    FROM fromjsonb
)
SELECT CASE WHEN type = 'int4range' THEN element::int4range
            -- ELSE element::text
            END
FROM trim;

   case    
-----------
 [-75,-69)
(1 row)

In my example it selects the data and that can't work because PostgreSQL will not let you select a first row as int4range then the following one as another type.

But you can use this same logic to insert data in another table (which I assume is your goal) and that will work.