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

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

jian.universality@xxxxxxxxx wrote:

bryn@xxxxxxxxxxxx wrote:

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

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

...

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 ———————————————————————————————— */

create temp table source(
  ...
  CONSTRAINT test_jsonb_constraints1 check (book_info_text is json)
  CONSTRAINT test_jsonb_constraints2 check (JSON_EXISTS(book_info_text::jsonb,'$.title') )
  ...
  )
);

Some of the problems I don't know how to solve. My intuition feels like that isbn attribute in the json document column then enforcing the unique constraint would be anti-pattern. So I put the isbn outside as a separate column. Another constraint is that there are only certain keys in the jsonb. I don't know how to implement it. But I feel like it's do-able.
two columns, one text, another generated column stored jsonb, So there is a duplication issue...

So there is another alternative way to do it...

Thank you very much for your suggestions, Jian. I'll try them and think carefully about how everything then looks over the next week or so.

Meanwhile, I hope that it's clear to all that I have nothing more than prototype code on my own laptop. I can change any aspect of it in no time when I come to see better alternatives. (In particular, I can easily heave out my use of "domains with not null constraints".)

My code, as it stands, does meet the goals that I set for it (esp. by meeting the "JSON → relational → JSON" idempotency requirement). I also handle the "required", or "not required", status of the attributes of the objects (and only specified keys present) quite easily. But I don't see any sign of "only specified keys present" in your code.

I haven't tried my prototype with large volumes of synthetic data—and nor have I considered performance at all. That can come later. (But with my current implementation, my tiny end-to-end test kit completes crazily quickly.)

I probably didn't say out loud that the kinds of updates and queries that the four table Codd-and-Date representation suggests can be supported with fairly ordinary SQL and not too much thought. However (at least as it feels to me), the corresponding operations on the native "jsonb" representation would be harder to design and write. Moreover, *any* change implies updating all of the indexes and re-checking all of the constraints.

In other words, my aim here is to treat JSON in the way that first motivated it (as a data transport format) and *not* in the way that it's often used (as a bucket for a set of noticeably heterogeneous documents). This is where the the "JSON → relational → JSON" idempotency requirement comes from.

Sorry if I didn't make this clear.