Json

JSON Path support in Postgres 12

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.

Converting query results to JSON

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.