Cast text from json to Date-CodePudding

I have this json field and i want the result as a date.

select t.config_exit::json ->> 'earliest_exit' as Earliest_Exit
from table t

This give me null or for example 2021-11-03 as result but as a text field.

select t.config_exit::json ->> 'earliest_exit'::date as Earliest_Exit
    from table t

Ive tried this but it doesn't work.

CodePudding user response：

Put the json expression between parenthesis, e.g.

SELECT (t.config_exit ->> 'earliest_exit')::date
FROM t;

Demo: db<>fiddle

WITH t (config_exit) AS (
  VALUES ('{"earliest_exit":"2021-11-03"}'::jsonb)
)
SELECT (t.config_exit ->> 'earliest_exit')::date
FROM t;

    date    
------------
 2021-11-03

It will fail in case earliest_exit is empty, so you might want to filter them out before casting. Alternatively you can use to_date, but it would maybe give you dates you don't want:

WITH t (config_exit) AS (
  VALUES ('{"earliest_exit":"2021-11-03"}'::jsonb),
         ('{"earliest_exit":""}'::jsonb),
         ('{"earliest_exit":null}'::jsonb)
)

SELECT to_date(t.config_exit ->> 'earliest_exit','YYYY-MM-DD')
FROM t;

    to_date    
---------------
 2021-11-03
 0001-01-01 BC
 
(3 Zeilen)