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

On Sat, Jun 18, 2022 at 5:44 AM Bryn Llewellyn <bryn(at)yugabyte(dot)com> 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