BUG #14449: inconsistency in behaviour of json/jsonb returning functions for sql NULL and json NULL values

From: rainer(dot)steinmueller(at)kit(dot)edu
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14449: inconsistency in behaviour of json/jsonb returning functions for sql NULL and json NULL values
Date: 2016-12-05 17:53:54
Message-ID: 20161205175354.4360.29511@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14449
Logged by: Rainer Steinmüller
Email address: rainer(dot)steinmueller(at)kit(dot)edu
PostgreSQL version: 9.6.1
Operating system: Debian 3.16.36-1+deb8u2 x86_64 GNU/Linux
Description:

# all the following sql statements are tested with psql.
# try to cast or to convert a sql NULL value to json/jsonb null does not
generate the desired result:

net=> select to_jsonb(null::text);
to_jsonb
----------

(1 row)

# this is the correct result:

net=> select jsonb_build_object('f1', null) -> 'f1';
?column?
----------
null
(1 row)

# the same behaviour for manipulating jsonb:
# this statement does not generate the desired result and worst, it destroys
the whole jsonb data:

net=> select jsonb_set('{"f1":1,"f2":"abc"}', '{f1}', null);
jsonb_set
-----------

(1 row)

# the following does well (but works only for jsonb due to the ||
operator):

net=> select '{"f1":1,"f2":"abc"}'::jsonb || jsonb_build_object('f1',
null);
?column?
---------------------------
{"f1": null, "f2": "abc"}
(1 row)

# problem: how to set a jsonb null value over more than 1 level (the path
array in arg2 has more than 1 element) like this:
# select jsonb_set('{"f1":1,"f2":"abc","f3": {}}', '{f3,f4}', null);

# in my opinion, json/jsonb returning functions
# to_json(), to_jsonb(), jsonb_set(), jsonb_insert()
# should never return sql null values if they receive a sql null argument
(as value that is to convert implicitly).

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2016-12-05 18:27:09 Re: BUG #14448: create table as values - type check skipped
Previous Message VICTOR MIGUEL MORALES CAYUELA 2016-12-05 14:37:22 Re: Segmentation fault in PQexec