Search code examples
postgresqljsonb

Querying Postgres SQL JSON Column

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
Solution

  • 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%';