Extracting dates from PostgreSQL database

Question

The db is PostgreSQL 9.6.

I'm creating an app that has events, and the user chooses a date / time that he wants to attend.

So, I created the following structure:

CREATE TABLE public.intervals (
 id int4 PRIMARY KEY, 
 event_id int4 NULL,
 date_start date,
 date_end date, 
 configuration jsonb DEFAULT '{}',
)

I've chosen to put data into a jsonb column instead of creating a new table ( configuration ). There, I put an array of objects, like this:

[ { "id": "f53382aa-86b6-e442-8de2-f8c457e5ef11",
 "capacity": 2,
 "duration": 30,
 "end_hour": "12:00",
 "start_hour": "08:00",
 "days_of_week": [ 4, 5 ] },
 { "id": "3d68b5de-7594-fdb9-2848-1190688dd532",
 "capacity": 2,
 "duration": 10,
 "end_hour": "18:00",
 "start_hour": "14:00",
 "days_of_week": [ 1, 2, 3 ] } ]

Now, I want to extract the dates from this data, and I think I'm getting there, but it's getting too hard.

Am I making a mistake using jsonb to store the configuration?

Here's a fiddle of the table and some example data, in case someone wants to take a look: http://sqlfiddle.com/#!17/99d30/3/1

Thanks!

Edit:

I think I got it:

http://sqlfiddle.com/#!17/99d30/44/0

-- select * from intervals;
with days as(
 select
 generate_series(
 date_start,
 date_end,
 '1 day'
 ) as day,
 i.configuration
 from
 intervals i
),
lines as(
 select
 day,
 jsonb_array_elements(configuration) as config,
 ((jsonb_array_elements(configuration)->>'days_of_week')::jsonb) @> ('"' || extract(dow from day)::text || '"')::jsonb as dow_enabled
 from
 days 
) 
 select
 generate_series(
 (
 day::date::text || ' ' ||(
 config ->> 'start_hour'
 )::text
 )::timestamp,
 (
 day::date::text || ' ' ||(
 config ->> 'end_hour'
 )::text
 )::timestamp,
 (
 l.config ->> 'duration' || ' minutes'
 )::interval
 ) as event_date, l.config ->> 'capacity' as capacity
from
 lines l
 where dow_enabled = true

It works and it looks fast, but I'm open to suggestions.

• Evan Carroll: I'm not sure what you mean when you say extract dates can you gives us some sample data and desired output.

Answer 1

Am I making a mistake using jsonb to store the configuration?

Yes. You're querying on it. That's usually an indicator to me that you're abusing JSONB.

A few other notes,

1. On your jsonb formatting. I've cleaned it up. Using JSON, "4" is not the same as 4. You should store numbers as numbers and not as strings
2. Your default for configuration is {}, but your sample data shows [{},{}]. It's bad practice to store some arrays-of-objects, and some objects in the top level. Pick one.