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

[jian he <jian.universality@xxxxxxxxx>]

On Sat, Jun 18, 2022 at 5:44 AM Bryn Llewellyn <bryn@xxxxxxxxxxxx> wrote:

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

create temp table source(
    isbn text primary key,
    book_info_text text,
    book_info jsonb generated always as ( book_info_text::jsonb ) stored
    CONSTRAINT
        test_jsonb_constraints1 check (book_info_text is json)
    CONSTRAINT    test_jsonb_constraints2 check (JSON_EXISTS(book_info_text::jsonb,'$.title') )
    CONSTRAINT    test_jsonb_constraints3 check (JSON_VALUE(book_info_text::jsonb,'$.year' returning int) > 0)
    CONSTRAINT    test_jsonb_constraints4 check (JSON_EXISTS(book_info_text::jsonb,'$.genre'))
    CONSTRAINT    test_jsonb_constraints5 check (not JSON_EXISTS(book_info_text::jsonb,'$.not_as_toplevel_key'))
    CONSTRAINT    test_jsonb_constraints6 check (
            (JSON_VALUE(book_info_text::jsonb,'$.authors[*]."family name"') is not null)
            OR
            (JSON_VALUE(book_info_text::jsonb,'$.authors[*]."given name"' ) is not null)
         )
);

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.

normal relation tables, insert is done via json_table construct convert json to table. output can be easily done with row_to_json.

For example:

select * from json_table('{
  "title"   : "Joy Luck Club",
  "year"    : 2006,
  "authors" : [{"given name": "Amy", "family name" : "Tan"}],
  "genre"   : "Novel"
  }'::jsonb,
    '$'
    COLUMNS(
        id for ordinality,
        title text path '$.title',
        year int path '$.year',
        genre text path '$.genre',
        nested path '$.authors[*]'
        columns(
            "given name" text path '$."given name"'
            ,"family name" text path '$."family name"'
        )
    )
);

--

 I recommend David Deutsch's <<The Beginning of Infinity>>

  Jian