Thread: bug in jsonb_set porstgresql 5.5
Hello
Pascal BARBEDOR
I noticed a problem with a jsonb field and jsonb_set function
- table1 is a table with history a jsonb field, and obs a text field which is null
Those three instructions were sent to postgresql
1- this one is ok, set the field to an empty object
update table1 set history=‘{}’
2- this one is ok, creates a key q1 initialized at an empty object
update table1 set history=jsonb_set(history,’{q1}’, ‘{}’)
3- this one resets completely the jsonb field, not only the key obs is not set to null but the entire jsonb field is now null
update table1 set history=jsonb_set(history,’{q1,obs}’, to_jsonb(obs))
this is on postgresql 5.5
Best regards
Pascal BARBEDOR
tel 01 45 34 55 25
mob 06 82 32 69 63
tel 01 46 23 86 74
fax 09 72 42 01 93HelloI noticed a problem with a jsonb field and jsonb_set function- table1 is a table with history a jsonb field, and obs a text field which is null
[...]
3- this one resets completely the jsonb field, not only the key obs is not set to null but the entire jsonb field is now nullupdate table1 set history=jsonb_set(history,’{q1,obs}’, to_jsonb(obs))
Working as designed - jsonb_set and to_jsonb are both defined as "NULL ON NULL INPUT (i.e., STRICT)" - since "obs [is] a text field which is null" the final result of the function call is NULL.
David J.
Pascal Barbedor schrieb am 22.02.2018 um 14:38:
> I noticed a problem with a jsonb field and jsonb_set function
>
> - table1 is a table with *history* a jsonb field, and *obs* a text field which is null
>
> Those three instructions were sent to postgresql
>
> 1- this one is ok, set the field to an empty object
> *update table1 set history=‘{}’*
>
> 2- this one is ok, creates a key q1 initialized at an empty object
> *update table1 set history=jsonb_set(history,’{q1}’, ‘{}’)*
>
> *3- this one resets completely the jsonb field, not only the key obs is not set to null but the entire jsonb field is
nownull*
> *
> *
> *update table1 set history=jsonb_set(history,’{q1,obs}’, to_jsonb(obs))*
>
> this is on postgresql 5.5
There is no PostgreSQL 5.5 (and never was if I'm not mistaken)
Thanks for the answer
Where is this mentioned in the doc ?
Since it is only a key inside the json object which is assigned the null value, does it mean it is impossible to have null values inside the jsonb object ?
Le 22 févr. 2018 à 16:42, David G. Johnston <david.g.johnston@gmail.com> a écrit :HelloI noticed a problem with a jsonb field and jsonb_set function- table1 is a table with history a jsonb field, and obs a text field which is null[...]3- this one resets completely the jsonb field, not only the key obs is not set to null but the entire jsonb field is now nullupdate table1 set history=jsonb_set(history,’{q1,obs}’, to_jsonb(obs)) Working as designed - jsonb_set and to_jsonb are both defined as "NULL ON NULL INPUT (i.e., STRICT)" - since "obs [is] a text field which is null" the final result of the function call is NULL.David J.
Thanks for the answerWhere is this mentioned in the doc ?Since it is only a key inside the json object which is assigned the null value, does it mean it is impossible to have null values inside the jsonb object ?
The docs don't indicate whether functions are strict or not (nor apparently does psql's \df+)...but the behavior of strict functions is documented (at least in CREATE FUNCTION for when you are creating your own).
It is indeed impossible to place an SQL NULL inside of a json object - you can place a json null inside a json object though - thus you need to convert from SQL NULL to json null explicitly (typically via COALESCE):
jsonb_set(history, '{q1,obs}', COALESCE(to_jsonb(obs), 'null'))
David J.