0
votes

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
1
Your query and your error message don't match.jjanes
thank you for pointing this out. When I corrected the where statement in the query (see edited question) it now returns empty columns.B.H.
The #>> operator needs an array as the right hand argument, e.g. json_col #>> '{event1,START_DATE}'a_horse_with_no_name
What exactly is the output you want? Both "events" would qualify for being active. 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
The goal is I want to query event_type across both events and have a response that indicates the start_date for both events. Is that possible?B.H.

1 Answers

1
votes

It sounds like you want to unnest your json structure, ignoring the top level keys and just getting the top level values. You can do this with jsonb_each, looking at resulting column named 'value'. You would put the function call in the FROM list as a lateral join (but since it is a function call, you don't need to specify the LATERAL keyword, it is implicit)

select value->>'START_DATE' from data, jsonb_each(json_col) 
    where value->>'event_type' like '%active%';