Bread Bread - 1 year ago 52
JSON Question

Cast type given a type name

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


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
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
FROM trim;

(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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download