How to get a JSON property value using PostgreSQL

Last modified:

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  |

> operator

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.

Follow @vlad_mihalcea