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;