[GTALUG] JSON Standard for SQL Thoughts

[Christopher Browne]

On Fri, 5 Jun 2020 at 17:23, Nicholas Krause via talk <talk at gtalug.org>
wrote:

> Greetings,
>
> I'm aware a few people here keep up more with certain database trends
> then me including Chris Browne. I'm aware of the newest standard of
> SQL supporting JSON.  Does anyone have any thoughts on this or how
> it may affect things going forward in the database world.
>
> Postresql supported operators before but seems their moving to the
> current SQL standard implementation, last I checked.


The PostgreSQL JSON types had been getting some pretty positive
attention, and with what got drawn into JSONB around the 9.5 days
https://www.postgresql.org/docs/9.5/functions-json.html
made it pretty attractive in ways that XML never got to.

JSONB was interesting in that it compresses material and provides
indexing inside the JSON document, so you can have functional
indexes on parts of the content, as well as GIN indexes

Sensible use of it shouldn't make all the usual relational bits go
away; tis best to define a table that has JSON with some mix
of JSON and other stuff.

ntdb=# create table our_users( user_id serial primary key);
CREATE TABLE
ntdb=#
ntdb=# create table message_encoded_json (
ntdb(#   id serial primary key,
ntdb(#   user_id integer not null references our_users(user_id) on delete
restrict,
ntdb(#   created_on timestamptz default now(),
ntdb(#   json_blob jsonb
ntdb(# );
CREATE TABLE
ntdb=# create index msg_gin on message_encoded_json using gin (json_blob
jsonb_ops);
CREATE INDEX
ntdb=# create index msg_gin2 on message_encoded_json using gin (json_blob
jsonb_path_ops);
CREATE INDEX

I'm not at all sure which of those indexes are preferable; that hasn't made
it into my
knowledge.

The above is the sort of pattern that makes sense; if there's a wild bundle
of stuff that
might go into a table of billing transactions, you'd still have ordinary
traditional attributes
like a transaction ID, transaction types, and dates in the lifecycle that
are pretty
universal.

I think I was in one of Oleg Bartunov's talks on their own "jsonpath" query
language,
possibly this one https://www.pgcon.org/2019/schedule/events/1274.en.html

The query system for JSON bits seemed pretty brain-melty; remembering what
little sets of characters going together searched for whatever seemed
painfully
similar to, oh, say, APL.

https://github.com/obartunov/sqljsondoc/blob/master/README.jsonpath.md
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://gtalug.org/pipermail/talk/attachments/20200605/596fb6f5/attachment.html>