Imagine having a tool that can automatically detect JPA and Hibernate performance issues.
Hypersistence Optimizer is that tool!
In this article, I’m going to explain how you can get a JSON property value using the ->> PostgreSQL operator. This way, we can transform a JSON object that’s stored in a json or jsonb column into a virtual relational database table.
While it’s a good idea to design the database schema according to the relational model, there are also situations when the relational model is way too strict.
For instance, as I explained in this article, when implementing an audit log mechanism using database triggers, it’s very convenient to store the old and new row snapshots in JSON columns, as this strategy will allow future target table structure modifications without having to change the audit table itself. More, when using JSON columns, we can even use a single audit log table to store change events that are coming from multiple tables.
The book table stores all the books in our library, and the book_audit_log table stores the CDC (Change Data Capture) events that are generated whenever a book record is changed via an INSERT, UPDATE, or DELETE DML statement.
Let’s assume that the book_audit_log table contains the following data:
Now, we want to extract all snapshot versions of a given book record. For this, we can use the new_row_data JSON column, but instead of returning the results in JSON format, we want to recreate the book table records from the new_row_data JSON objects.
Using the PostgreSQL ->> operator, we can get a JSON property value and include it in the SQL query projection, as illustrated by the following SQL query:
dml_timestamp AS version_timestamp,
new_row_data ->> 'title' AS title,
new_row_data ->> 'author' AS author,
new_row_data ->> 'price_in_cents' AS int
) AS price_in_cents,
new_row_data ->> 'publisher' AS publisher
book_audit_log.book_id = :bookId
So, when running the above SQL query, we get the exact result set we wanted:
JSON column types are very useful when we need to store non-structured data, which’s the case for the audit log table because JSON columns work much better than the strict relation model.
And, thanks to the PostgreSQL ->> operator, we can get the JSON property value from the JSON or JSONB column and include it in the SQL query projection. Afterward, we can practically take the newly created virtual table and benefit from all the SQL features provided by PostgreSQL to further transform the data according to our current business requirements.