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

> mmoncure(at)gmail(dot)com <mailto:mmoncure(at)gmail(dot)com> wrote:
>
> yeah. I would expect for json or jsonb, two values, "a, b", "a" is distinct from "b" should give the same answer as "a::text is distinct from b::text".
>
>> tgl(at)sss(dot)pgh(dot)pa(dot)us <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>>> bryn(at)yugabyte(dot)com <mailto:ryn(at)yugabyte(dot)com> wrote:
>>>
>>> I'm going to try to think like this: The number of possible spellings of the names of keys in a JSON object is some flavor of infinite. So including this in an object:
>>>
>>> "k": null
>>>
>>> really is saying something. It says that I do know about "k" and that yet I have simply no information available about its value.
>>
>> I'd read it as asserting that key "k" is meaningful for this object, but the correct value for that key is not known.
>>
>> I have a hard time with your assertion that {"x": 42, "y": null} should be considered equivalent to {"x": 42}, because it would
>> render key-exists predicates useless. Either you have to say that key "y" is claimed to exist in both of these objects and indeed every object, or you have to make it fail if the key's value is null (so that it'd say "false" in both of these cases). Either of those options seems both weird and useless.
>>
>>> The quirkiness that my first example showed can be seen differently from how I saw it first. When I now consider this expression:
>>>
>>> ('{"x": 42}'::jsonb)->>'y'
>>>
>>> it seems that its evaluation should simply raise an exception. But you said:
>>>
>>>> This produces "key y not present in JSON" but someone decided that was too unfriendly and so we instead produce SQL NULL.
>>
>> Right. This is hard to justify from a purist semantic point of view, but having the operator throw an error in such cases would make it close to unusable on not-uniformly-structured data. And really the point of using JSON inside a SQL database is to cope with irregularly-structured data, so fuzziness seems like what we want.

Thank you very much for this, Tom. And thanks again to you, David, for your input. I hope that it's clear that the purpose of my questions is to discover what I'm missing—both w.r.t. actual semantics and w.r.t. the use cases that motivate PostgreSQL's functionality. Sorry if my questions (here and on other topics) might seem to challenge established wisdom and precedent.

Thank you very much for this, Tom. And thanks again to you, David, for your earlier input. I hope that it's clear that the purpose of my questions is to discover what I'm missing—both w.r.t. actual semantics and w.r.t. the use cases that motivate PostgreSQL's functionality. Sorry if, contrary to my intention, my questions (here and on other topics) might seem to challenge established wisdom and precedent.

I delayed my reply until I'd had time to think, to do some study, and (most importantly) to implement a complete, self-contained proof-of-concept to substantiate my conclusion. I'm ready, now, to report back.

Summary: I can meet my goal by using PG's native functionality appropriately.

So, w.r.t. this email’s subject, this:

select strip_null_keys('{"x": 42, "y": null}'::jsonb) = '{"x": 42}'::jsonb;

returns "true".

The point at issue is whether the presence of « "some key": null » is different from the absence of "some key". And you (all) have argued that the two locutions for what I have wanted to see as one notion are indeed different. Well, yes, of course they are. This is tautologically true if you think only of the Unicode text of a JSON document that's so far waiting to be ingested by who knows what system. And you pointed out that, in PostgreSQL

my_doc ? 'some key'

detects the difference. You've convinced me that some use cases will care about this—in other words, it's a distinction *with* a difference. But, in my use case, the two distinct locutions bring no semantic difference. But the actual distinction causes me a problem when I try to prove that this pair of transformations is idempotent:

JSON → relational → JSON

But that problem is behind me now.

So... (and if you read this far at all) you can stop now unless you're interested to read further.

Before getting to my use case, I noted that Tom said this about the fact that using ->> to read a non-existent key returns a SQL null:

> [This] is hard to justify from a purist semantic point of view, but having the operator throw an error in such cases would make it close to unusable on not-uniformly-structured data.

It's easy to fix this, when the app requires strictness, by implementing a user-defined operator pair, say +>> and its partner. I did this (to respect what my use case needs) —and it passed all my tests. However, it turned out, for reasons that I explain below, that I didn't need it. The operator's implementation function reads the value with the appropriate native operator and only if it returns SQL null (or JSON null) does it do more testing. First it checks if the key is absent with the ? operator—raising an error if it is so. Then it reads the value (again) with the -> native operator and raises an error if it gets a JSON null.

————————————————————

My use case

I should stress that what I have, so far, is simply a demo implementation on my laptop. All the tests that I've managed to invent (including the idempotency test) work as I expect them to. However, any aspect of my code can be changed in a heartbeat if I realize that it's suspect.

Here's an example of the documents that I have in my table's "jsonb" column.

{
"isbn" : "978-0-14-303809-2",
"title" : "Joy Luck Club",
"year" : 2006,
"authors" : [
{"given name": "Amy", "family name" : "Tan"}
],
"genre" : "Novel"
}

The documents are very definitely supposed to adhere to a JSON Schema.

https://json-schema.org/

But I don't need the formality of JSON Schema's notation for such a simple case. Prose will do fine.

/* ————— START OF SPEC —————————————————————————————— */

The document's top-level object may use only these keys:

"isbn" — string
values must be unique across the entire set of documents (in other words, it defines the unique business key); values must have this pattern:

« ^[0-9]{3}-[0-9]{1}-[0-9]{2}-[0-9]{6}-[0-9]{1}$ »

"title" — string

"year" — number
must be a positive integral value

"authors" — array of objects;
must be at least one object

"genre" — string

Each object in the "authors" array object may use only these keys:

"family name" — string

"given name" — string

String values other than for "isbn" are unconstrained.

Any key other than the seven listed here is illegal. The "genre" and "given name" keys are not required. All the other keys are required.

The meaning of *required* is that no extracted value must bring a SQL null (so a required key must not have a JSON null value).

And the meaning of *not required* is simply "no information is available for this key" (with no nuances). The spec author goes further by adding a rule: this meaning must be expressed by the absence of such a key.

/* ————— END OF SPEC ———————————————————————————————— */

The rule that « "some key": null » is not allowed brings the benefit that a document can be maximally terse. Moreover, it helps document authors by removing the need to decide which locution to use; and it helps programmers to write code to check that incoming documents adhere to the spec and then to extract their meaning (for example to a classic relational representation).

It's easy to see that the information content implies these business rules:

—Each book must have at least one author. Each author may be among the authors of one or several books.

—Each book may be of exactly one (known) genre. Each genre may classify one or several books.

My code does this:

—Ingests the input JSON documents into a "source(k... primary key, book_info jsonb)" table.

—Shreds the books facts into a classic Codd-and-Date relational representation (with the obvious tables "books", "genres", "authors", and "books_authors" with the usual PK and FK constraints.

—Transforms the aggregated set of facts for each book back to a set of JSON documents for transport to a different system. Critically, these must adhere to the same JSON Schema (and rules) that govern the incoming documents.

This is where the idempotency requirement that I mentioned above comes from:

JSON → relational → JSON

It's been suggested that this is an ignoble and unattainable goal. I disagree—on both counts.

I implemented two complementary functions:

—"no_null_keys()" checks that a "jsonb" value has no occurrences of « "some key": null »

—"strip_null_keys()" removes « "some key": null » occurrences from a "jsonb" value

The code checks with "no_null_keys()" that, as expected, no ingested JSON document has an occurrence of « "some key": null ».

And it uses "strip_null_keys()" on the output of "to_jsonb()" — and, as appropriate, any other built-in JSON function that produces a "jsonb" value.

It was straightforward to implement these two functions by using REGEXP built-in functionality on the canonically formatted "text" value produced by the "jsonb::text" typecast.

The check on the incoming documents is included in the "j_books_book_info_is_conformant(jsonb)" function that is the basis of a constraint that's created on the "source" table's "book_info" column.

More code is needed to implement other constraints like, for example, the value of the "isbn" (string) key must satisfy a particular regular expression and the value of the "year" (number) key must convert to a positive integer (You've heard about that test already.)

These tests, too, (and other tests) are included in the "j_books_book_info_is_conformant(jsonb)" function.

Critically, one test uses "jsonb_object_keys()" to scan the top-level object to ensure that all the required keys are present, that every key has the specified JSON data type, and that no keys that the JSON Schema doesn't mention are present. A similar test does the same for the "authors" array. This is why I can be sure that the native ->> and -> operators are sufficient for my purpose.

I considered using the "j_books_book_info_is_conformant(jsonb)" function in a constraint for a domain based on "jsonb" but decided against that.