OTRS - list of agents per tickets

The issue

OTRS is a ticketing system using a PostgreSQL backend. The question was "let's take all the tickets... can you give me a list of all agents who have done something on each ticket, ordered by the time they did something".

The naive approach looks like (I'm filtering on a specific ticket ID to limit the output):


SELECT ticket_id, JSONB_AGG(login ORDER BY ticket_history.create_time ASC)
FROM ticket_history
LEFT JOIN users ON ticket_history.change_by = users.id
WHERE ticket_history.ticket_id = 1207734
GROUP BY ticket_id

which leads to something like:

ticket_id jsonb_agg
1207734 ["root@localhost", "root@localhost", "my.login", "my.login", "my.login", "root@localhost" ]

I'm here using some PostgreSQL feature which allows to order arguments inside an aggregate function, available starting from PostgreSQL 9.0.

It's obviously not false, but obviously not optimal - we want to remove the duplicates in this JSONB output.

The solution

Windows functions to the rescue!

SELECT  
    ticket_id,
    JSONB_AGG(login) FROM (
        SELECT 
            ticket_id,
            login, 
            LAG(login) 
        OVER (ORDER BY create_time ASC) AS prev_login
        FROM (
            SELECT 
                ticket_id,
                ticket_history.create_time, 
                users.login
            FROM ticket_history
            LEFT JOIN users ON ticket_history.change_by = users.id
            WHERE ticket_history.ticket_id = 1207734
        ) AS F
) AS foo 
WHERE 
    login != prev_login OR 
    prev_login IS NULL
GROUP BY ticket_id

which leads to expected output, removing duplicates:

ticket_id jsonb_agg
1207734 ["root@localhost", "my.login", "root@localhost" ]