Postgresql – Extracting dates from PostgreSQL database

database-designjsonpostgresql

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.

Best Answer

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.
Related Question