Re: Getting a delta of two json-typed objects? (a breeze w/ hstore)

Hi, not so long ago, I've asked myself the same question. I've written two fuction that do this:

SELECT build_json(array_agg(to_json(old.key)), array_agg(old.value))
FROM json_each($1) old
LEFT OUTER JOIN json_each($2) new ON old.key = new.key
WHERE old.value::text <> new.value::text OR new.key IS NULL
HAVING array_agg(to_json(old.key)) IS NOT NULL
AND array_agg(old.value) IS NOT NULL;

and this:

CREATE OR REPLACE FUNCTION pgmemento.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;

Not the best way actually. I wonder, how I could benefit from the new build_json function in 9.4
json_build_object(VARIADIC"any"). Have to get my keys and values in alternating order... hm. 

Then I've also found this nice example, which might do the things you are looking for:
http://schinckel.net/2014/05/25/querying-json-in-postgres/
 
Ahoi
Felix


Gesendet: Donnerstag, 22. Januar 2015 um 20:37 Uhr
Von: "Wells Oliver" <wellsoliver@gmail.com>
An: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Betreff: [GENERAL] Getting a delta of two json-typed objects? (a breeze w/ hstore)

Hey all. I have a trigger function which does a delta of two hstore values just doing a - b; this check is performed to
seeif there's a delta and if not I don't log it. 
 
I'm wondering if there's a suitable method for comparison two json objects? I don't have 9.4 yet so I can't use jsonb,
butif there's any input here I'd appreciate it. 
 
Thanks.
 --
Wells Oliver
wellsoliver@gmail.com