Querying Postgres SQL JSON Column

Question

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

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.
– B.H., Commented Apr 1, 2020 at 18:02
The #>> operator needs an array as the right hand argument, e.g. json_col #>> '{event1,START_DATE}' — user330315
– user330315, Commented Apr 1, 2020 at 18:07
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? — user330315
– user330315, Commented Apr 1, 2020 at 18:08
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.
– B.H., Commented Apr 1, 2020 at 18:13

jjanes · Accepted Answer · 2020-04-01 19:02:15Z

1

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

answered Apr 1, 2020 at 19:02

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

1 Comment

B.H. Over a year ago

This is what I was looking for! Thank you!

Collectives™ on Stack Overflow

Querying Postgres SQL JSON Column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related