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

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

hjp-pgsql@xxxxxx wrote:

Bryn wrote:

"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}$ »

Completely off-topic, but this regexp doesn't describe ISBNs. In ISBNs the three middle subfields are all variable length. The first is a language code (there are more than 10 languages in the world), the second identifies the publisher (there are more than 100 publishers) and the third the book. For example, "Mastering PostgreSQL 9.6" has the ISBN 978-1-78355-535-2.

Yes, I know. Sorry. I should have said that I simply wanted to illustrate a proof of concept for the notion, viable (only?) when you have incoming JSON documents with a well-defined schema, that this is idempotent:

  JSON → relational → JSON

And it's of note that PostgreSQL has had what you need to do the xform, in each direction, for a long time.

I simply typed up my "corpus" by hand. It didn’t matter that the ISBN format was a fairly tale. A book does have an ISBN and (as far as my P.o.C. is concerned) it's the unique business key. I should have cut out the REGEXP in my email and said something like "must conform to the specified format". But I was careless in my proof reading.

Thanks for not mentioning that books have publishers—so that "authors" isn't the only key that implies a Codd-and-Date many-to-many!