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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

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

Hi,

hstore have only key-value pairs, but in json can have same behavior -
only equal objects are removed:

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

'{"b": {"c": 2}}'

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

'{"a": 1}'

David

--
-------------------------------------
Ing. David TUROŇ
LinuxBox.cz, s.r.o.
28. rijna 168, 709 01 Ostrava

tel.: +420 591 166 224
fax: +420 596 621 273
mobil: +420 732 589 152
www.linuxbox.cz

mobil servis: +420 737 238 656
email servis: servis@linuxbox.cz
-------------------------------------

Od: David Fetter <david@fetter.org>
Komu: david.turon@linuxbox.cz
Kopie: pgsql-hackers@postgresql.org
Datum: 18. 07. 2017 18:24
Předmět: Re: [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 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 20 July 2017 at 16:24, David Fetter <david@fetter.org> wrote:

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.

I tried to embody some relevant thoughts in this thread [1]/messages/by-id/CA+q6zcU+gy1+dxQD09MSz8Zwqq+sPPfS-6GYKmyNqGVQDFeQbg@mail.gmail.com, I think it
would be great if
you can take a look and suggest something more.

[1]: /messages/by-id/CA+q6zcU+gy1+dxQD09MSz8Zwqq+sPPfS-6GYKmyNqGVQDFeQbg@mail.gmail.com
/messages/by-id/CA+q6zcU+gy1+dxQD09MSz8Zwqq+sPPfS-6GYKmyNqGVQDFeQbg@mail.gmail.com