Json

Suport for JSON is ubiquitous in modern relational database. Since the introduction of JSON support in th SQL:2016 standard, accessing (nested) JSON values is very similar across database products using SQL/JSON path expressions.

PostgreSQL was the first relational database to support JSON and corresponding JSON functions. With version 12 it also supports SQL/JSON path.

For certain types of conditions, the PostgreSQL specific operators and functions are more powerful than the SQL/JSON path functions.

The SQL:2016 standard introduced JSON and various ways to query JSON values. Postgres has been supporting JSON for a long time but defined its own set of operators and functions to query JSON values.

With PostgreSQL 12, the standard JSON path query functionality is now also supported by Postgres. Although the functions accepting JSON path queries do not conform to the SQL standard, the parameters and behavior does.

In my post about dynamic SQL I showed how PostgreSQL’s query_to_xml() could be used to run dynamic SQL and process the result in a regular SQL query.

If you prefer to deal with JSON, rather than XML, it’s quite easy to write a corresponding query_to_jsonb() function.