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" <[email protected]>
An: "[email protected]" <[email protected]>
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 see if 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, but if there's any input here I'd
appreciate it.
Thanks.
--
Wells Oliver
[email protected]
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
