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

The PostgreSQL JSON types had been getting some pretty positive

attention, and with what got drawn into JSONB around the 9.5 days

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.