Re: JSONB - JSONB operator feature request

On Wed, Jul 19, 2017 at 06:17:35PM -0500, Merlin Moncure wrote:
> On Tue, Jul 18, 2017 at 12:49 PM, David Fetter <david(at)fetter(dot)org> wrote:
> > On Tue, Jul 18, 2017 at 01:36:32PM +0200, david(dot)turon(at)linuxbox(dot)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