On Wed, Jul 19, 2017 at 06:17:35PM -0500, Merlin Moncure wrote: > On Tue, Jul 18, 2017 at 12:49 PM, David Fetter <da...@fetter.org> wrote: > > On Tue, Jul 18, 2017 at 01:36:32PM +0200, david.tu...@linuxbox.cz wrote: > >> Hi, > >> > >> some users and me used hstore - hstore for example storing only changed > >> rows in trigger like: > >> > >> hsore(NEW) - hstore(OLD) > >> > >> There isn't same operator/function in JSON/JSONB. We can only remove keys > >> from JSONB, but not equal key-value pairs. Is there any chance to have > >> same feature with JSON/JSONB in postgres core? > > > > Here's one slightly modified from > > http://coussej.github.io/2016/05/24/A-Minus-Operator-For-PostgreSQLs-JSONB/ > > > > CREATE OR REPLACE FUNCTION jsonb_minus ( arg1 jsonb, arg2 jsonb ) > > RETURNS jsonb > > LANGUAGE sql > > AS $$ > > SELECT > > COALESCE(json_object_agg( > > key, > > CASE > > -- if the value is an object and the value of the second > > argument is > > -- not null, we do a recursion > > WHEN jsonb_typeof(value) = 'object' AND arg2 -> key IS NOT NULL > > THEN jsonb_minus(value, arg2 -> key) > > -- for all the other types, we just return the value > > ELSE value > > END > > ), '{}')::jsonb > > FROM > > jsonb_each(arg1) > > WHERE > > arg1 -> key IS DISTINCT FROM arg2 -> key > > $$; > > > > CREATE OPERATOR - ( > > PROCEDURE = jsonb_minus, > > LEFTARG = jsonb, > > RIGHTARG = jsonb > > ); > > > > I suspect that there's a faster way to do the jsonb_minus function > > internally. > > yes, please! I also sorely miss the hstore 'slice' function which is > very similar. The main remaining disadvantage with jsonb WRT to > hstore is that you can't do simple retransformations that these > operations allow for. Too often you end up doing multiple '->' > operations against the same object followed by a rebundling which is a > real performance killer.
If we can agree to a definition, we can make this go. My vague memories from graph theory indicate that that "agree to a definition" part is the real problem to be solved. Best, David. -- David Fetter <david(at)fetter(dot)org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers