Thread: Why does jsonb_set() remove non-mentioned keys?
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?
I would expect {"foo": 1, "bar": null} or no change at all to the original JSON value, but not that the whole JSON is
setto null.
In the original case the new value to be set was the result of an expression, not a "hardcoded" null value.
Thomas
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.
David G. Johnston schrieb am 04.07.2019 um 18:20:
> On Thu, Jul 4, 2019 at 6:18 AM Thomas Kellerer <spam_eater@gmx.net <mailto: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.
But jsonb_set() doesn't change (or shouldn't) the whole value, only one key.
I can understand that the "bar" key would not be set (because of the NULL), but removing a key that isn't even part of
thetarget path looks like a bug to.
Thomas
Some experimentation:
> \pset null '((null))'
> select jsonb_set('{"foo":1}'::jsonb,'{bar}','null'::jsonb,true);
┌─────────────────────────┐
│ jsonb_set │
├─────────────────────────┤
│ {"bar": null, "foo": 1} │
└─────────────────────────┘
> select jsonb_set('{"foo":1}'::jsonb,'{bar}',to_jsonb(null::text),true);
┌───────────┐
│ jsonb_set │
├───────────┤
│ ((null)) │
└───────────┘
That's a bit weird already. Also:
> select null::jsonb;
┌──────────┐
│ jsonb │
├──────────┤
│ ((null)) │
└──────────┘
> select 'null'::jsonb;
┌───────┐
│ jsonb │
├───────┤
│ null │
└───────┘
> select to_jsonb(null::int);
┌──────────┐
│ to_jsonb │
├──────────┤
│ ((null)) │
└──────────┘
> select to_jsonb('null'::text);
┌──────────┐
│ to_jsonb │
├──────────┤
│ "null" │
└──────────┘
I'm sharing Thomas's confusion…
--
Dakkar - <Mobilis in mobile>
GPG public key fingerprint = A071 E618 DD2C 5901 9574
6FE2 40EA 9883 7519 3F88
key id = 0x75193F88
Work continues in this area.
-- DEC's SPR-Answering-Automaton
On Thursday, July 4, 2019, Gianni Ceccarelli <dakkar@thenautilus.net> wrote:
Some experimentation:
> \pset null '((null))'
> select jsonb_set('{"foo":1}'::jsonb,'{bar}','null'::jsonb,true);
┌─────────────────────────┐
│ jsonb_set │
├─────────────────────────┤
│ {"bar": null, "foo": 1} │
└─────────────────────────┘
No SQL null, ok
> select jsonb_set('{"foo":1}'::jsonb,'{bar}',to_jsonb(null::text), true);
┌───────────┐
│ jsonb_set │
├───────────┤
│ ((null)) │
└───────────┘
Sql null poisons the expression and so sql null is the result
That's a bit weird already. Also:
> select null::jsonb;
┌──────────┐
│ jsonb │
├──────────┤
│ ((null)) │
└──────────┘
Sql null
> select 'null'::jsonb;
┌───────┐
│ jsonb │
├───────┤
│ null │
└───────┘
Json null
> select to_jsonb(null::int);
┌──────────┐
│ to_jsonb │
├──────────┤
│ ((null)) │
└──────────┘
Sql null poisons the function call which immediately returns sql null
> select to_jsonb('null'::text);
┌──────────┐
│ to_jsonb │
├──────────┤
│ "null" │
└──────────┘
Json null
I'm sharing Thomas's confusion…
Sql null and json null are represented differently; strict functions with sql null inputs yield sql null output without even executing the function
David J.
On Thu, Jul 4, 2019 at 2:09 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thursday, July 4, 2019, Gianni Ceccarelli <dakkar@thenautilus.net> wrote:> select to_jsonb('null'::text);
┌──────────┐
│ to_jsonb │
├──────────┤
│ "null" │
└──────────┘Json null
Sorry, my bad on this last one. You cannot use to_jsonb to construct a json null. The only way, I think, to construct a json null scalar is with an explicit literal.
SELECT 'null'::jsonb;
The to_jsonb function always interprets a textual value passed to it as being the literal text and so the original query results in a json string whose content is "null"
David J.
Aha! I had mis-understood how "strict"-ness works.
Thank you David for the explanation!
Thomas: the two main pieces are these:
> SQL null and json null are represented differently
As far as SQL is concerned, `'null'::jsonb` is a valid (non-`NULL`)
value. The SQL part of Postgres doesn't "look inside" the jsonb value,
the same way it doesn't "look inside" numbers or strings or
whatever. It only cares if they're `NULL` or not, and then it passes
them to functions and operators (ok, it does look at boolean values
for `WHERE` clauses, but even `ORDER BY` is handled by comparison
operators)
> strict functions with sql null inputs yield sql null output without
> even executing the function
So when the SQL-level executor sees a call to any function declared
strict with some NULL parameters, it doesn't call the function at
all. `whatever_my_function('a string',1234,NULL)` is always `NULL`
--
Dakkar - <Mobilis in mobile>
GPG public key fingerprint = A071 E618 DD2C 5901 9574
6FE2 40EA 9883 7519 3F88
key id = 0x75193F88
Gianni Ceccarelli schrieb am 05.07.2019 um 10:00:
>> strict functions with sql null inputs yield sql null output without
>> even executing the function
>
> So when the SQL-level executor sees a call to any function declared
> strict with some NULL parameters, it doesn't call the function at
> all. `whatever_my_function('a string',1234,NULL)` is always `NULL`
>
Ah, I see. Thanks for the clarification
Then I would question if declaring jsonb_set as "strict" makes sense
Thomas
On 2019-07-05 10:59:31 +0200, Thomas Kellerer wrote:
> Gianni Ceccarelli schrieb am 05.07.2019 um 10:00:
> >> strict functions with sql null inputs yield sql null output without
> >> even executing the function
> >
> > So when the SQL-level executor sees a call to any function declared
> > strict with some NULL parameters, it doesn't call the function at
> > all. `whatever_my_function('a string',1234,NULL)` is always `NULL`
> >
>
> Ah, I see. Thanks for the clarification
>
> Then I would question if declaring jsonb_set as "strict" makes sense
I think it does but I raise the same question for to_jsonb. It's defined
on anyelement and the documentation says:
| Returns the value as json or jsonb. Arrays and composites are converted
| (recursively) to arrays and objects; otherwise, if there is a cast from
| the type to json, the cast function will be used to perform the
| conversion; otherwise, a scalar value is produced. For any scalar type
| other than a number, a Boolean, or a null value, the text representation
| will be used, in such a fashion that it is a valid json or jsonb value.
The documentation explicitely singles out "a number, a Boolean, or a
null value", but doesn't specify how they are treated. I would expect
that they are treated equivalently, though: An SQL number is converted
to a JSON number, an SQL boolean is converted to JSON true or false and
an SQL null is converted to JSON null. Returning SQL null instead of a
JSON null breaks that expectation for no discernible reason. It also
isn't consistent, since an SQL null in an array or composite is
converted to a JSON null, as I would expect.
hp
--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>