Thread: comparing two JSON objects in 9.3

comparing two JSON objects in 9.3

From
"Felix Kunde"
Date:
Ahoi

 

I'm developing an auditing mechanism where table contents are logged as JSON (github.com/fxku/audit).

At first I just logged the whole row everytime my triggers were fired.

Now I'm trying to log only the old state of values that have changed.

 

Therefore I compare the OLD and NEW entry by using json_each(json).

 

SELECT old.key, old.value
FROM json_each(row_to_json(OLD)) old
LEFT OUTER JOIN json_each(row_to_json(NEW)) new ON old.key = new.key
WHERE new.value::text <> old.value::text OR new.key IS NULL;

 

Now I aggregate the result to arrays of JSON and call this function, that I've written:


CREATE OR REPLACE FUNCTION audit.build_json(json_keys ANYARRAY, json_values ANYARRAY) RETURNS JSON AS
$$
DECLARE
  json_string TEXT := '{';
  delimeter TEXT := '';
  json_result JSON;
BEGIN
  FOR i IN array_lower(json_keys, 1)..array_upper(json_keys, 1) LOOP
    json_string := json_string || delimeter || json_keys[i] || ':'
||json_values[i]; 
    delimeter := ',';
  END LOOP;

  json_string := json_string || '}';

  EXECUTE format('SELECT %L::json', json_string) INTO json_result;
  RETURN json_result;
END
$$
LANGUAGE plpgsql;

 

In the end the call looks like this:

SELECT audit.build_json(array_agg(to_json(old.key)), array_agg(old.value))

FROM json_each(row_to_json(OLD)) old
LEFT OUTER JOIN json_each(row_to_json(NEW)) new ON old.key = new.key
WHERE new.value::text <> old.value::text OR new.key IS NULL;


 

Everything works as expected, but it feels kinda ugly to me.

Any PG-JSON experts around to tell me a better solution?

Did i trapped into some "YouShouldNot"s?

 

Regards!

Felix Kunde

Re: comparing two JSON objects in 9.3

From
"yuri.ivanenko@progforce.com"
Date:
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.


Re: comparing two JSON objects in 9.3

From
"yuri.ivanenko@progforce.com"
Date:
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.