SQL/JSON in PG15 !
Slide from my talk Understanding Jsonb performance at PGCONF.NYC, Dec 2, 2021, with some corrections.
I learned about new version of SQL standard (Fall of 2016, SQL-2016) with SQL/JSON Specification and instantly bought electronic copy of this standard. I spent several days reading it and decided to start project "SQL/JSON in PostgreSQL". It was obvious that our #JSONB group should work on this and I decided this project should be Postgres Professional contribution to #PostgreSQL community. First patches were ready in 2017, jsonpath was committed in 2019 for PG13. Now, after several years other patches are on the way to PG15 !
Four years ago I wrote documentation with examples for SQL/JSON
https://github.com/obartunov/sqljsondoc/blob/master/README.jsonpath.md
It would be very nice to have other patches Pluggable TOAST in PG15, that would helpus with JSONB performance.
Several recent commits make me happy, thanks Andrew Dunstan !
commit f4fb45d15c59d7add2e1b81a9d477d0119a9691a
Author: Andrew Dunstan
AuthorDate: Thu Mar 3 13:02:10 2022 -0500
Commit: Andrew Dunstan
CommitDate: Sun Mar 27 17:03:34 2022 -0400
SQL/JSON constructors
This patch introduces the SQL/JSON standard constructors for JSON:
...................
commit f79b803dcc98d707450e158db3638dc67ff8380b
Author: Andrew Dunstan
AuthorDate: Thu Mar 3 13:00:49 2022 -0500
Commit: Andrew Dunstan
CommitDate: Sun Mar 27 17:03:33 2022 -0400
Common SQL/JSON clauses
This introduces some of the building blocks used by the SQL/JSON
constructor and query functions. Specifically, it provides node
executor and grammar support for the FORMAT JSON [ENCODING foo]
clause, and values decorated with it, and for the RETURNING clause.
..................
commit 33a377608fc29cdd1f6b63be561eab0aee5c81f0
Author: Andrew Dunstan
AuthorDate: Thu Mar 3 13:02:53 2022 -0500
Commit: Andrew Dunstan
CommitDate: Mon Mar 28 15:37:08 2022 -0400
IS JSON predicate
This patch intrdocuces the SQL standard IS JSON predicate. It operates
on text and bytea values representing JSON as well as on the json and
jsonb types. Each test has an IS and IS NOT variant. The tests are:
IS JSON [VALUE]
IS JSON ARRAY
IS JSON OBJECT
IS JSON SCALAR
IS JSON WITH | WITHOUT UNIQUE KEYS
.....................
More patches will follow in the coming days, most anticipated is JSON_TABLE!