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. We are going to use the same database tables we employed when implementing an audit log table using PostgreSQL triggers and JSONB columns: More, when using JSON columns, we can even use a single audit log table to store change events that are coming from multiple tables. 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.įor 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. This way, we can transform a JSON object that’s stored in a json or jsonb column into a virtual relational database table.

In this article, I’m going to explain how you can get a JSON property value using the -> PostgreSQL operator. So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night! Well, Hypersistence Optimizer is that tool!Īnd it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework. Follow having a tool that can automatically detect JPA and Hibernate performance issues.

Postgresql json query array