On Fri, Jun 3, 2022 at 6:41 PM Bryn Llewellyn <bryn@xxxxxxxxxxxx> 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.
