comparing two JSON objects in 9.3
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Ahoi</div>
<div> </div>
<div>I'm developing an auditing mechanism where table contents are logged as JSON (github.com/fxku/audit).</div>
<div>At first I just logged the whole row everytime my triggers were fired.</div>
<div>Now I'm trying to log only the old state of values that have changed.</div>
<div> </div>
<div>Therefore I compare the OLD and NEW entry by using json_each(json).</div>
<div> </div>
<div>SELECT old.key, old.value<br/>
FROM json_each(row_to_json(OLD)) old<br/>
LEFT OUTER JOIN json_each(row_to_json(NEW)) new ON old.key = new.key<br/>
WHERE new.value::text <> old.value::text OR new.key IS NULL;</div>
<div> </div>
<div>Now I aggregate the result to arrays of JSON and call this function, that I've written:</div>
<div>
<div>CREATE OR REPLACE FUNCTION audit.build_json(json_keys ANYARRAY, json_values ANYARRAY) RETURNS JSON AS<br/>
$$<br/>
DECLARE<br/>
json_string TEXT := '{';<br/>
delimeter TEXT := '';<br/>
json_result JSON;<br/>
BEGIN<br/>
FOR i IN array_lower(json_keys, 1)..array_upper(json_keys, 1) LOOP<br/>
json_string := json_string || delimeter || json_keys[i] || ':' || json_values[i];<br/>
delimeter := ',';<br/>
END LOOP;</div>
<div> json_string := json_string || '}';</div>
<div> EXECUTE format('SELECT %L::json', json_string) INTO json_result;<br/>
RETURN json_result;<br/>
END<br/>
$$<br/>
LANGUAGE plpgsql;</div>
<div> </div>
<div>In the end the call looks like this:</div>
<div>SELECT audit.build_json(array_agg(to_json(old.key)), array_agg(old.value))</div>
<div>FROM json_each(row_to_json(OLD)) old<br/>
LEFT OUTER JOIN json_each(row_to_json(NEW)) new ON old.key = new.key<br/>
WHERE new.value::text <> old.value::text OR new.key IS NULL;</div>
</div>
<div> </div>
<div>Everything works as expected, but it feels kinda ugly to me.</div>
<div>Any PG-JSON experts around to tell me a better solution?</div>
<div>Did i trapped into some "YouShouldNot"s?</div>
<div> </div>
<div>Regards!</div>
<div>Felix Kunde</div></div></body></html>
Wow. This is exactly what I'm looking for . The standard audit from here
https://wiki.postgresql.org/wiki/Audit_trigger is not good enough for me - I
want to audit only changed values and ignore all other ones.
Thank you Felix!
Regards,
Yuri.
--
View this message in context: http://postgresql.nabble.com/comparing-two-JSON-objects-in-9-3-tp5803617p5896917.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
There's also another way using hstore instead of json:
https://wiki.postgresql.org/wiki/Audit_trigger_91plus
--
View this message in context: http://postgresql.nabble.com/comparing-two-JSON-objects-in-9-3-tp5803617p5897128.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general