Re: Getting a delta of two json-typed objects? (a breeze w/ hstore)
От | Felix Kunde |
---|---|
Тема | Re: Getting a delta of two json-typed objects? (a breeze w/ hstore) |
Дата | |
Msg-id | trinity-aef6b2dc-62ea-4abe-a8b5-c52102f612c5-1421956825951@3capp-gmx-bs33 обсуждение исходный текст |
Ответ на | Getting a delta of two json-typed objects? (a breeze w/ hstore) (Wells Oliver <wellsoliver@gmail.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: