CODEWITHSUNDEEP
arraysjsonpostgresql
mik
mik

Reputation: 111

Processing results from plpgsql function

I have PostgreSQL function witch returns this json array as text:

[{"seq_no":5796,"start_date":null,"end_date":"2008-09-30 12:32:28","geom_change":"Y"}, 
 {"seq_no":8235,"start_date":"2008-09-30 12:32:28","end_date":"2008-10-02 16:43:24","geom_change":"N"}, 
 {"seq_no":9306,"start_date":"2008-10-02 16:43:24","end_date":"2008-10-02 18:31:09","geom_change":"N"}, 
 {"seq_no":9754,"start_date":"2008-10-02 18:31:09","end_date":"2008-10-07 17:08:25","geom_change":"N"}, 
 {"seq_no":10701,"start_date":"2008-10-07 17:08:25","end_date":"2008-10-08 15:17:48","geom_change":"N"}, 
 {"seq_no":8940,"start_date":"2008-10-08 15:17:48","end_date":"2008-10-08 15:51:47","geom_change":"N"}, 
 {"seq_no":12500,"start_date":"2008-10-08 15:51:47","end_date":"2008-10-08 17:34:35","geom_change":"N"}, 
 {"seq_no":13079,"start_date":"2008-10-08 17:34:35","end_date":"2008-10-08 17:56:03","geom_change":"N"}]

I want to use this data to select seq_no filtered by start/end dates. A preferred result would be table like this.

seq_no   start_date             end_date               geom_change
------------------------------------------------------------------
5796     NULL                   2008-09-30 12:32:2     Y
8235     2008-09-30 12:32:28    2008-10-02 16:43:24    N

Or maybe there is simpler way to use this data to select seq_no between start_date and end_date?

Upvotes: 0

Views: 26

Answers (1)

Mike Organek
Mike Organek

Reputation: 12494

You can cast your result as jsonb and then use the jsonb functions and operators along with a tzrange as follows:

with indata (jdata) as (
  values (
'[{"seq_no":5796,"start_date":null,"end_date":"2008-09-30 12:32:28","geom_change":"Y"}, 
 {"seq_no":8235,"start_date":"2008-09-30 12:32:28","end_date":"2008-10-02 16:43:24","geom_change":"N"}, 
 {"seq_no":9306,"start_date":"2008-10-02 16:43:24","end_date":"2008-10-02 18:31:09","geom_change":"N"}, 
 {"seq_no":9754,"start_date":"2008-10-02 18:31:09","end_date":"2008-10-07 17:08:25","geom_change":"N"}, 
 {"seq_no":10701,"start_date":"2008-10-07 17:08:25","end_date":"2008-10-08 15:17:48","geom_change":"N"}, 
 {"seq_no":8940,"start_date":"2008-10-08 15:17:48","end_date":"2008-10-08 15:51:47","geom_change":"N"}, 
 {"seq_no":12500,"start_date":"2008-10-08 15:51:47","end_date":"2008-10-08 17:34:35","geom_change":"N"}, 
 {"seq_no":13079,"start_date":"2008-10-08 17:34:35","end_date":"2008-10-08 17:56:03","geom_change":"N"}]'::jsonb
 )
)
select (j->>'seq_no')::int as seq_no, 
       (j->>'start_date')::timestamp as start_date, 
       (j->>'end_date')::timestamp as end_date,
       j->>'geom_change' as geom_change
  from indata  i
       cross join lateral jsonb_array_elements(jdata) as e(j)
 where tsrange((j->>'start_date')::timestamp, (j->>'end_date')::timestamp, '[]') @> '2008-09-30 12:32:28'::timestamp;

db<>fiddle here

Upvotes: 1

Related Questions