From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | david(dot)turon(at)linuxbox(dot)cz |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: JSONB - JSONB operator feature request |
Date: | 2017-07-18 17:49:24 |
Message-ID: | 20170718174924.GH11340@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Rofail | 2017-07-18 17:51:07 | Re: GSoC 2017: Foreign Key Arrays |
Previous Message | Mark Rofail | 2017-07-18 17:48:10 | Re: GSoC 2017: Foreign Key Arrays |