Re: [HACKERS] JSONB - JSONB operator feature request
> 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
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
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
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 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 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
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
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
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 -