| From: | "Felix Kunde" <felix-kunde(at)gmx(dot)de> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | comparing two JSON objects in 9.3 |
| Date: | 2014-05-12 09:18:13 |
| Message-ID: | trinity-f03554db-477f-45a8-8543-9fc5752fdec4-1399886293028@3capp-gmx-bs43 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
<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>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 2.3 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Souquieres Adam | 2014-05-12 12:57:15 | 8.4 -> 9.1 : ANALYSE VERBOSE; -> out of shared memory |
| Previous Message | David G Johnston | 2014-05-12 01:18:41 | Re: XML validation of whitespace values |