'{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really?

From: Bryn Llewellyn <bryn(at)yugabyte(dot)com>
To: pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: '{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really?
Date: 2022-06-04 01:41:43
Message-ID: 7E0BF7DF-CC12-48B6-8550-9C2B5A8F78B1@yugabyte.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Here’s the minimal testcase:

do $body$
declare
j1 constant jsonb not null := '{"x": 42, "y": null}';
j2 constant jsonb not null := '{"x": 42 }';

predicate_1 constant boolean not null := (j1->>'y' is null) AND (j2->>'y' is null);
predicate_2 constant boolean not null := j1 = j2;
begin
assert predicate_1;
assert not predicate_2;
end;
$body$;

The block finishes silently.

I certainly expect "predicate_1" to be true. This reflects the defined JSON semantics that, within an object, the omission of a key-value pair is the same as its presence with a value equal to the (bizarre) JSON null.

As I reason it, the truth of "predicate_1" implies the truth of "predicate_2" because "jsonb" claims to represent the underlying semantics of a JSON document using its own secret post-parse representation.

Am I missing a use case where an object with a key-value pair with a JSON null value is meaningfully different from one where the key is simply absent?

If not, would you consider what I showed to be evidence of a bug?

My discovery let me design other tests.

This block confirms the basic idea that the meaning of a "jsonb" value is independent of the formatting of the incoming document that defined it.

do $body$
declare
t1 constant text not null := '
{
"x": 42,
"y": 17
}
';
j1 constant jsonb not null := t1::jsonb;
t2 constant text not null := j1::text;
j2 constant jsonb not null := t2::jsonb;

predicate_3 constant boolean not null := t2 = t1;
predicate_4 constant boolean not null := j2 = j1;
begin
assert not predicate_3;
assert predicate_4;
end;
$body$;

Another test (I won't bore you with its code) confirms that the order in which the incoming document lists key-value pairs has no consequence for its meaning.

Here's another twist on the same basic issue:

create type t_ as (a int, b int);
create function f()
returns table(z text)
language plpgsql
as $body$
declare
j1_in constant jsonb not null := '{"a": 17, "b": null}';
j2_in constant jsonb not null := '{"a": 17 }';

r1 constant t_ not null := jsonb_populate_record(null::t_, j1_in);
r2 constant t_ not null := jsonb_populate_record(null::t_, j2_in);

j1_out constant jsonb not null := to_jsonb(r1);
j2_out constant jsonb not null := to_jsonb(r1);
begin
z := j1_out::text; return next;
z := j2_out::text; return next;
end;
$body$;
select f();

This is the result:

{"a": 17, "b": null}
{"a": 17, "b": null}

I have a little demo where I shred a set of "books" incoming JSON documents (where a book has a genre and many authors) into the classic Codd-and-Date four tables: books, authors, genres, and book_author_intersections. Then I scrunch each book back to a single JSON doc. I want to prove that I get back what I started with. So I have to clutter what should be a simple test:

differ constant boolean not null :=
(
with
a as (select * from j_in except select * from j_out),
b as (select * from j_out except select * from j_in )

select (exists(select 1 from a) or exists(select 1 from b))
);

with no end of ad-hoc-ery.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2022-06-04 02:01:11 Re: '{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really?
Previous Message Reid Thompson 2022-06-03 22:38:35 Re: Why password authentication failed for user "postgres"?