dedupe_events_1.sql

-- This is slow, and you don't want to use it!
--
-- Filter an array of events such that there is only one event with each event_id.
-- When more than one event with the same event_id is present, take the latest one.
CREATE OR REPLACE FUNCTION dedupe_events_1(events HSTORE[]) RETURNS HSTORE[] AS $$
  SELECT array_agg(event)
  FROM (
    -- Filter for rank = 1, i.e. select the latest event for any collisions on event_id.
    SELECT event
    FROM (
      -- Rank elements with the same event_id by position in the array, descending.
      SELECT events[sub] AS event, sub, rank()
      OVER (PARTITION BY (events[sub] -> 'event_id')::BIGINT ORDER BY sub DESC)
      FROM generate_subscripts(events, 1) AS sub
    ) deduped_events
    WHERE rank = 1
    ORDER BY sub ASC
  ) to_agg;
$$ LANGUAGE SQL IMMUTABLE;