Contents
To get all changes per audit_id of one transaction as one row of JSONB you can use the pgmemento.jsonb_merge function as an aggregate or window function. When combining it with an ordering by the row_log ID it is possible to see the first or the last changes per field.
sql
SELECT
r.audit_id,
pgmemento.jsonb_merge(r.old_data ORDER BY r.id) AS first_changes,
pgmemento.jsonb_merge(r.old_data ORDER BY r.id DESC) AS last_changes
FROM
pgmemento.row_log r
JOIN
pgmemento.table_event_log e
ON e.event_key = r.event_key
WHERE
e.transaction_id = 1000000
GROUP BY
r.audit_id;