I have a json column (json_col) in a postgres database with the following structure:
{
"event1":{
"START_DATE":"6/18/2011",
"END_DATE":"7/23/2011",
"event_type":"active with prior experience"
},
"event2":{
"START_DATE":"8/20/11",
"END_DATE":"2/11/2012",
"event_type":"active"
}
}
[example of table structure][1]
How can I make a select statement in postgres to return the start_date and end_date with a where statement where "event_type" like "active"?
Attempted Query:
select person_id, json_col#>>'START_DATE' as event_start, json_col#>>'END_DATE' as event_end
from data
where json_col->>'event_type' like '%active%';
Returns empty columns.
Expected Response:
event_start
6/18/2011
8/20/2011
#>>
operator needs an array as the right hand argument, e.g.json_col #>> '{event1,START_DATE}'
– a_horse_with_no_nameactive
. Which start and end date do you want to retrieve? Can you please edit your question and add the expected output? – a_horse_with_no_name