Re: JSONB - JSONB operator feature request

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.

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