Multiple records as result of inner join on array values

Question

I have two tables in Postgres:

events (id, occurrence_dates) # occurrence_dates is array
dates_calendar (date)

When events.occurrence_dates consists of a few equal values, {"2017-11-08 00:00:00","2017-11-08 00:00:00"} 2 values as example, following query gives a single record:

SELECT "events".* 
FROM "events" 
   INNER JOIN dates_calendar dc ON dc.date = ALL(occurrence_dates)

How to get as many records as length of events.occurrence_dates?

UPD: i'm using Ruby on Rails, but question is provided in SQL context

Rails scope based on @michel.milezzi answer:

scope :all_events, -> do
  select("events_sb.*").from(<<-SQL.squish)
    dates_calendar dc
    INNER JOIN (SELECT *, UNNEST(occurrence_dates) oc_date FROM events) AS events_sb
    ON (events_sb.oc_date = dc.date)
  SQL
end

Michel Milezzi · Accepted Answer · 2017-11-08T10:41:16.763

2

You can use UNNEST to expand the array and then use a regular join:

-- CTE with test data
WITH dates_calendar(date) AS (
    VALUES 
        ('2017-11-08 00:00:00'::TIMESTAMP), 
        ('2017-11-09 00:00:00'), 
        ('2017-11-10 00:00:00')
), events (id, occurrence_dates) AS (
    VALUES
        (1, '{"2017-11-08 00:00:00", "2017-11-08 00:00:00","2017-11-09 00:00:00","2017-11-10 00:00:00"}'::TIMESTAMP[]), 
        (2, '{"2017-11-08 00:00:00","2017-11-09 00:00:00"}'), 
        (3, '{"2017-11-08 00:00:00"}')
), events_sb AS (
    SELECT id, UNNEST(occurrence_dates) oc_date FROM events
)
SELECT 
    events_sb.* 
FROM 
    dates_calendar dc JOIN events_sb ON (events_sb.oc_date = dc.date) ORDER BY id;

--CTE
WITH events_sb AS (
    SELECT id, UNNEST(occurrence_dates) oc_date FROM events
)
SELECT 
    events_sb.* 
FROM 
    dates_calendar dc JOIN events_sb ON (events_sb.oc_date = dc.date) ORDER BY id;    

--SUBQUERY
SELECT 
    events_sb.* 
FROM 
    dates_calendar dc JOIN (SELECT id, UNNEST(occurrence_dates) oc_date FROM events) AS events_sb ON (events_sb.oc_date = dc.date) ORDER BY id;

To call it in Rails you must do a plain sql, such explained here and here.

edited Nov 08 '17 at 10:41

answered Nov 07 '17 at 13:38

Michel Milezzi

10,087
3
21
36

thanks! Though i didn't figured out how to use it with ActiveRecord (Ruby on Rails), the idea works well with raw SQL. WITH events_sb AS ( SELECT *, UNNEST(occurrence_dates) oc_date FROM EVENTS ) SELECT events_sb.* FROM dates_calendar dc JOIN events_sb ON (events_sb.oc_date = dc.date); – y.bregey Nov 08 '17 at 09:02
@yurko You must create a [plain select](https://stackoverflow.com/a/14840547/7925366). – Michel Milezzi Nov 08 '17 at 10:28
Why this answer was downvoted? Please comment explaining so we can discuss about it. – Michel Milezzi Nov 08 '17 at 10:44
because of hardcoded dates in example. later I tried your query without test data and it worked, but I was not able to cancel downvote before edit is accepted. thanx, upvoted now – y.bregey Nov 09 '17 at 08:34
No problem, glad to help you @yurko – Michel Milezzi Nov 09 '17 at 10:40

score 0 · Answer 2 · edited Nov 07 '17 at 14:35

0

Is occurrence_dates a json data type? If yes, you could simply use the json_populate_recordset() function.

edited Nov 07 '17 at 14:35

Michel Milezzi

10,087
3
21
36

answered Nov 07 '17 at 13:25

Breathe

714
5
21

no, it is array `default '{}'::timestamp without time zone[]` – y.bregey Nov 07 '17 at 13:35

Multiple records as result of inner join on array values

2 Answers2