The EDB Blog
30. August 2019

SQL/JSON is coming to PostgreSQL 12

SQL/JSON is coming to PostgreSQL 12 and provides a native way to query JSON data—to be specific, JSON data stored as a JSONB data type. This also introduces the jsonpath data type which is used for SQL/JSON query expressions. I will not be going into its usage in detail or covering performance characteristics in this post, but I will compare the syntax and functionality with jsquery. Note: This may potentially change prior to final release.

jsquery was introduced as a non-core extension for PostgreSQL 9.4 and higher by Teodor Sigaev, Alexander Korotkov and Oleg Bartunov. Like jsonpath, it also used its own datatype, jsquery.

Functions to Pass jsonpath Expressions

We have some functions to which we can pass jsonpath expressions, and 2 have operators which can be used as shorthand (albeit without additional parameter control, as they exist primarily for indexing purposes): 

These will suppress errors where there is a lack of an array element, object field, an unexpected JSON type, or numeric errors.

To view examples of how jsquery and SQL/JSON differ in practice, read more on Postgres Rocks, our community for all things Postgres.'s picture

Thom is a Principal Systems Engineer at EnterpriseDB, providing advanced support, training, consultancy and comprehensive system assessments to produce customised architectural health check reports. In addition to this, Thom is also heavily involved in the PostgreSQL community answering...