On Fri, 5 Jun 2020 at 17:23, Nicholas Krause via talk <talk@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