how to extract date from JSON data type column in Postgres

Question

I have a column open_houses that's JSON data type and it looks like this:

open_houses
[{"Date": "2017-08-13", "ToTime": "4:00PM", "FromTime": "2:00PM"}]
[{"Date": "2017-08-12", "ToTime": "3:00PM", "FromTime": "1:00PM"}]
[{"Date": "2017-08-12", "ToTime": "4:00PM", "FromTime": "2:00PM"}]
[{"Date": "2017-08-13", "ToTime": "3:00PM", "FromTime": "1:00PM"}]

When I try to extract 'Date' from this column I get an error -

SELECT ...
FROM   prod.vw_listing
WHERE  ...
       ...
       AND open_houses[1] -> 'Date'::DATE >= current_date;

Error:

cannot subscript type jsonb because it is not an array

Clodoaldo Neto · Accepted Answer · 2017-08-23 16:12:23Z

8

Use the -> operand to get a json array element:

with vw_listing (open_houses) as ( values 
    ('[{"Date": "2017-08-13", "ToTime": "4:00PM", "FromTime": "2:00PM"}]'::jsonb),
    ('[{"Date": "2017-08-12", "ToTime": "3:00PM", "FromTime": "1:00PM"}]'),
    ('[{"Date": "2017-08-12", "ToTime": "4:00PM", "FromTime": "2:00PM"}]'),
    ('[{"Date": "2017-08-13", "ToTime": "3:00PM", "FromTime": "1:00PM"}]')
)
select (open_houses -> 0 ->> 'Date')::date
from vw_listing
;
    date    
------------
 2017-08-13
 2017-08-12
 2017-08-12
 2017-08-13

answered Aug 23, 2017 at 16:12

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hky404 · Accepted Answer · 2017-08-23 16:20:59Z

5

I was able to resolve this by doing this:

SELECT ...

FROM   prod.vw_listing
WHERE  ...
       AND (open_houses -> 0 ->> 'Date')::date>= current_date;

answered Aug 23, 2017 at 16:20

hky404

1,1694 gold badges20 silver badges35 bronze badges

Comments

Arthur Sult · Accepted Answer · 2018-08-09 11:45:22Z

0

Also you can use this select:

select (open_houses#>> '{0, Date}')::date

It is equeal to selecting element like open_houses[0]['Date']

answered Aug 9, 2018 at 11:45

Arthur Sult

7367 silver badges10 bronze badges

Collectives™ on Stack Overflow

how to extract date from JSON data type column in Postgres

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related