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

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

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.