comparing two JSON objects in 9.3
От | Felix Kunde |
---|---|
Тема | comparing two JSON objects in 9.3 |
Дата | |
Msg-id | trinity-f03554db-477f-45a8-8543-9fc5752fdec4-1399886293028@3capp-gmx-bs43 обсуждение исходный текст |
Ответы |
Re: comparing two JSON objects in 9.3
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: