Postgres Pro
Downloads
Home   >   mailing lists

Re: Why does jsonb_set() remove non-mentioned keys? - Mailing list pgsql-general

From David G. Johnston
Subject Re: Why does jsonb_set() remove non-mentioned keys?
Date
Msg-id CAKFQuwaEErw0+DH6w+XwzbSMYiWdcRO5Bcy2ts_1C6FTB0TxMw@mail.gmail.com
Whole thread Raw
In response to Why does jsonb_set() remove non-mentioned keys?  (Thomas Kellerer <spam_eater@gmx.net>)
Responses Re: Why does jsonb_set() remove non-mentioned keys?
List pgsql-general
Tree view
On Thu, Jul 4, 2019 at 6:18 AM Thomas Kellerer <spam_eater@gmx.net> wrote:
Why does

   select jsonb_set('{"foo": 1}'::jsonb, '{bar}', to_jsonb(null::int), true)

return NULL when all it should do is to add a second key?

Both functions involved are defined as being STRICT (null on null input).  You need to COALSESCE SQL NULL to JSON 'null' to obtain the result you desire.  This is a general truth when dealing with the JSON type in PostgreSQL.

select jsonb_set('{"foo": 1}'::jsonb, '{bar}', coalesce(to_jsonb(null::int), 'null'), true)

David J.

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: PostgreSQL upgrade from 9.4.2 to 9.6.12
Next
From: Ben Snaidero
Date:
Subject: Re: pg_dump (COPY) hanging intermittently