SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
;with old(id, a, b, c) as ( select id, a, b, c from (values (1, 'r', 123, true)) v (id, a, b, c) ) ,new as ( select * from (values (1, 'g', 0, false)) v (id, a, b, c) ) ,wChanges as ( select (select (row_to_json(o)) from old o) as j_old ,(select (row_to_json(n)) from new n) as j_new ) ,wChangeList as ( select o.key as column_name ,o.value as old_value ,n.value as new_value ,case when o.value = n.value then false else true end as changed from wChanges wc left join lateral (select * from json_each_text(wc.j_old) j ) o on true left join lateral (select * from json_each_text(wc.j_new) j ) n on n.key = o.key ) select json_agg( json_build_object( 'column', w.column_name ,'old', w.old_value ,'new', w.new_value ,'changed', w.changed ) ) from wChangeList w;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear