Re: [HACKERS] JSONB - JSONB operator feature request

[Dmitry Dolgov]

> On 20 July 2017 at 16:24, David Fetter  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], I think it
would be great if
you can take a look and suggest something more.

[1]:
https://www.postgresql.org/message-id/ca+q6zcu+gy1+dxqd09msz8zwqq+sppfs-6gykmynqgvqdfe...@mail.gmail.com

Re: [HACKERS] JSONB - JSONB operator feature request

[David Fetter]

On Wed, Jul 19, 2017 at 06:17:35PM -0500, Merlin Moncure wrote:
> On Tue, Jul 18, 2017 at 12:49 PM, David Fetter  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  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

Re: [HACKERS] JSONB - JSONB operator feature request

[Merlin Moncure]

On Tue, Jul 18, 2017 at 12:49 PM, David Fetter  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.

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

Re: [HACKERS] JSONB - JSONB operator feature request

[david . turon]

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: ser...@linuxbox.cz
-



Od: David Fetter <da...@fetter.org>
Komu:   david.tu...@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.tu...@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

Re: [HACKERS] JSONB - JSONB operator feature request

[David Fetter]

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.

Best,
David.
-- 
David Fetter  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

Re: [HACKERS] JSONB - JSONB operator feature request

[David Fetter]

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:
> 
> 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  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

[HACKERS] JSONB - JSONB operator feature request

[david . turon]

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?

Thanks!

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: ser...@linuxbox.cz
-