dedupe_events_2.sql

-- 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_2(events HSTORE[]) RETURNS HSTORE[] AS $$
  SELECT array_agg(event)
  FROM (
    -- select first of elements with the same event_id by position in the array, descending.
    SELECT first_value(event) as event
    OVER (PARTITION BY (event -> 'event_id')::BIGINT ORDER BY index DESC)
    FROM (
      -- Use unnest instead of generate_subscripts to turn an array into a set.
      SELECT event, row_number() as index
      OVER (ORDER BY event -> 'time')
      FROM unnest(events) AS event
    ) unnested_data
    ORDER BY index ASC
  ) deduped_events
$$ LANGUAGE SQL IMMUTABLE;