1_input_data.sql

CREATE TABLE f (id integer primary key, value text, inserted_at default current_timestamp);
INSERT INTO f VALUES(1,'{"a":1}','2022-05-16 04:24:48');
INSERT INTO f VALUES(2,'{"a":2}','2022-05-16 04:25:11');
INSERT INTO f VALUES(3,'{"a":2, "b":3}','2022-05-16 04:25:15');
INSERT INTO f VALUES(4,'{"a":2, "b":99}','2022-05-16 04:25:18');
INSERT INTO f VALUES(5,'{"a":3}','2022-05-16 04:25:32');

2_result.txt

+----+-----------------+---------+------------+--------------+------------------------+----------+
| id |      value      | fullkey | changed_to | changed_from | changed_to_inserted_at |   type   |
+----+-----------------+---------+------------+--------------+------------------------+----------+
| 5  | {"a":3}         | $.a     | 3          | 2            | 2022-05-16 04:25:32    | mutation |
| 5  | {"a":3}         | $.b     |            | 99           | 2022-05-16 04:25:32    | deletion |
| 4  | {"a":2, "b":99} | $.b     | 99         | 3            | 2022-05-16 04:25:18    | mutation |
| 3  | {"a":2, "b":3}  | $.b     | 3          |              | 2022-05-16 04:25:15    | addition |
| 2  | {"a":2}         | $.a     | 2          | 1            | 2022-05-16 04:25:11    | mutation |
| 1  | {"a":1}         | $.a     | 1          |              | 2022-05-16 04:24:48    | addition |
+----+-----------------+---------+------------+--------------+------------------------+----------+

3_json_diff.sql

with input as (
  select * from f
  order by inserted_at
),
neighbors as (
  select * from (
    select 
      input.id as previous_id,
      lead(input.id) over win as this_id,
      input.value as previous_value,
      lead(input.value) over win as this_value,
      lead(input.inserted_at) over win as changed_to_inserted_at
    from input
    window win as (order by inserted_at asc)
  ) as inner
  where inner.this_id is not null
),
mutations as (
  select
    neighbors.this_id,
    neighbors.this_value as value,
    prev.fullkey as fullkey,
    neighbors.this_value -> prev.fullkey as changed_to,
    prev.value as changed_from,
    neighbors.changed_to_inserted_at
  from
    neighbors,
    json_tree(neighbors.previous_value) as prev
  where prev.atom is not null
  and neighbors.this_value -> prev.fullkey is not null
  and neighbors.previous_value -> prev.fullkey is not null
  and neighbors.this_value -> prev.fullkey <> neighbors.previous_value -> prev.fullkey
),
additions as (
  select
    neighbors.this_id,
    neighbors.this_value as value,
    this.fullkey as fullkey,
    this.value as changed_to,
    null as changed_from,
    neighbors.changed_to_inserted_at
  from
    neighbors,
    json_tree(neighbors.this_value) as this
  where neighbors.previous_value -> this.fullkey is null
),
deletions as (
  select
    neighbors.this_id,
    neighbors.this_value as value,
    prev.fullkey as fullkey,
    null as changed_to,
    prev.value as changed_from,
    neighbors.changed_to_inserted_at
  from
    neighbors,
    json_tree(neighbors.previous_value) as prev
  where neighbors.this_value -> prev.fullkey is null
),
-- special case to get the key/values in only the first inserted row
initials as (
  select 
    input.id,
    input.value,
    init.fullkey,
    init.value as changed_to,
    null as changed_from,
    input.inserted_at as changed_to_inserted_at
  from
    (select * from input order by input.inserted_at limit 1) as input,
    json_tree(input.value) as init
  where init.atom is not null
)
select * from (
  select *, "addition" as type from initials
  union
  select *, "addition" as type from additions
  union
  select *, "deletion" as type from deletions
  union
  select *, "mutation" as type from mutations
)
order by changed_to_inserted_at desc