How to get a JSON property value using PostgreSQL
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
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.
Database tables
We are going to use the same database tables we employed when implementing an audit log table using PostgreSQL triggers and JSONB columns:
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:
| book_id | old_row_data | new_row_data | dml_type | dml_timestamp | dml_created_by | |---------|--------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------|----------|---------------------|----------------| | 1 | | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT | 2020-12-22 13:40:15 | Vlad Mihalcea | | 1 | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE | 2020-12-22 13:50:48 | Vlad Mihalcea | | 1 | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | | DELETE | 2020-12-22 14:05:33 | Vlad Mihalcea |
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:
SELECT dml_timestamp AS version_timestamp, new_row_data ->> 'title' AS title, new_row_data ->> 'author' AS author, cast( new_row_data ->> 'price_in_cents' AS int ) AS price_in_cents, new_row_data ->> 'publisher' AS publisher FROM book_audit_log WHERE book_audit_log.book_id = :bookId ORDER BY dml_timestamp
So, when running the above SQL query, we get the exact result set we wanted:
| version_timestamp | title | author | price_in_cents | publisher | |---------------------|-----------------------------------------------|---------------|----------------|-----------| | 2020-12-22 13:40:15 | High-Performance Java Persistence 1st edition | Vlad Mihalcea | 3990 | Amazon | | 2020-12-22 13:50:48 | High-Performance Java Persistence 1st edition | Vlad Mihalcea | 4499 | Amazon | | 2020-12-22 14:05:33 | | | | |
Awesome, right?
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
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.
