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

On Fri, Jun 3, 2022 at 6:41 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

>
>
>
>
> *declare j1 constant jsonb not null := '{"x": 42, "y":
> null}'; j2 constant jsonb not null := '{"x": 42 }';*
>

> *(j1->>'y' is null) *
>

This produces a JSON Null which when asked for as a text data type results
into SQL NULL due to implicit type coercion

> *(j2->>'y' is null)*
>

This produces "key y not present in JSON" but someone decided that was too
unfriendly and so we instead produce SQL NULL. This SQL NULL exists for a
different reason than the SQL NULL in the previous case. Unfortunately,
there is only a single concept of NULL in the SQL Language.

> * predicate_2 constant boolean not null := j1 = j2;*
>

This is trivially false, j1 has a key of "y" while j2 does not. If there
is a complaint to be had, this returning false isn't it.

> 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.
>

Yes, the equality test is semantic, do all the same keys and corresponding
values exist? If so, return true.

>
>
>
> * j1_in constant jsonb not null := '{"a": 17, "b":
> null}'; j2_in constant jsonb not null := '{"a": 17 }';*
> This is the result:
>
>
>
> *{"a": 17, "b": null}{"a": 17, "b": null}*
>

Yep, for basically the same reason as the first point. The nulls are
consequences of different situations (lack of key, value of key being null)
being largely undistinguishable at the SQL level. We provide a "does key
exists" operator if you do need to make that determination.

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.
>

Yeah, the lack of any goal of round tripping conversions from JSON through
SQL and back into SQL makes proving that the system does such a thing
problematic. You'll get a back something meaningfully equivalent, by your
own argument, but not identical on a key-by-key basis.

David J.