Thread: [HACKERS] JSONB - JSONB operator feature request

On Tue, Jul 18, 2017 at 01:36:32PM +0200, david.turon@linuxbox.cz wrote:
> Hi,
> 
> some users and me used hstore - hstore for example storing only changed 
> rows in trigger like:
> 
> hstore(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?

What would - mean precisely for JSON[B]?

For example, what would you expect

SELECT '{"a": 1, "b": {"c": 2}}'::JSONB - '{"b": 1, "b": {"c": 3}}'::JSONB

to yield?

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

On Tue, Jul 18, 2017 at 01:36:32PM +0200, david.turon@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
secondargument is           -- not null, we do a recursion           WHEN jsonb_typeof(value) = 'object' AND arg2 ->
keyIS 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.

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

On Tue, Jul 18, 2017 at 12:49 PM, David Fetter <david@fetter.org> wrote:
> On Tue, Jul 18, 2017 at 01:36:32PM +0200, david.turon@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.

I understand that there are more edge cases due the flexible json
structure but I'd be quite happy returning NULL or erroring when you
can't arrive at a sensible extraction.

merlin

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@fetter.org> wrote:
> > On Tue, Jul 18, 2017 at 01:36:32PM +0200, david.turon@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