'{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really? - Mailing list pgsql-general
| From | Bryn Llewellyn |
|---|---|
| Subject | '{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really? |
| Date | |
| Msg-id | 7E0BF7DF-CC12-48B6-8550-9C2B5A8F78B1@yugabyte.com Whole thread Raw |
| Responses |
Re: '{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really?
Re: '{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really? |
| List | 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.
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;
select (exists(select 1 from a) or exists(select 1 from b))
);
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.
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();
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}
{"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 )
(
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.
pgsql-general by date: