MySQL JSON_TABLE – Map a JSON object to a relational database table
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 the MySQL JSON_TABLE function works, and how you can use it to transform a JSON object into a relational database table.
When using a relational database system, it’s best to design the database schema according to the relational model. However, there are certain use cases when the relational model is too strict, and we are better off storing data in a JSON column type.
For instance, as I explained in this article, when designing an audit log table, it’s much more convenient to store the before and after row snapshots in JSON columns, as this strategy will accommodate future target table modifications while also avoiding to duplicate every target table column in the audit log table. Thanks to the JSON column approach, we can even use a single audit log table for all target tables that we are auditing.
Database tables
We are going to reuse the same database tables we used when implementing an audit log table using MySQL triggers and JSON columns:
The book
table stores all the books we have in our library, and the book_audit_log
table stores the CDC (Change Data Capture) events that were generated whenever a given book
record was changed via an INSERT, UPDATE, or DELETE SQL 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-10-21 11:55:11 | 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-10-21 11:55:12 | Vlad Mihalcea | | 1 | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | | DELETE | 2020-10-21 11:55:13 | Vlad Mihalcea |
Transforming a JSON object to a relational table with the MySQL JSON_TABLE function
Now, we want to extract all versions of a given book entity. For this, we can use the new_row_data
JSON column, but we don’t want to return the results in JSON format. Instead, we’d like to recreate the book
table structure associated with a new_row_data
entry.
Luckily, MySQL provides us the JSON_TABLE function, which we can use to transform a JSON object to a relational database table, as illustrated by the following SQL query:
SELECT book_audit_log.dml_timestamp as version_timestamp, r.* FROM book_audit_log, JSON_TABLE( new_row_data, '$' COLUMNS ( title VARCHAR(255) PATH '$.title', author VARCHAR(255) PATH '$.author', price_in_cents INT(11) PATH '$.price_in_cents', publisher VARCHAR(255) PATH '$.publisher' ) ) AS r WHERE book_audit_log.book_id = :bookId ORDER BY version_timestamp
When running the above SQL query, we get the following result set:
| version_timestamp | title | author | price_in_cents | publisher | |---------------------|-----------------------------------------------|---------------|----------------|-----------| | 2020-10-21 11:55:11 | High-Performance Java Persistence 1st edition | Vlad Mihalcea | 3990 | Amazon | | 2020-10-21 11:55:12 | High-Performance Java Persistence 1st edition | Vlad Mihalcea | 4499 | Amazon |
The JSON_TABLE function defines the virtual table column types and their associated JSON object properties where the column value will be extracted from.
While the MySQL syntax for the JSON_TABLE function resembles a CROSS JOIN, in reality, it behaves like a LATERAL JOIN as it joins as the book_audit_log
table rows with an underlying correlated subquery that extracts the virtual table records that are created by calling the JSON_TABLE function.
How to handle NULL JSON values when using the MySQL JSON_TABLE function
However, the result is not what we wanted since we didn’t get the row associated with the book record removal.
To include the book_audit_log
records having a NULL
new_row_data
column value, we can use LEFT JOIN between the book_audit_log
and the virtual table created by the JSON_TABLE function, as illustrated by the following SQL query:
SELECT book_audit_log.dml_timestamp as version_timestamp, r.* FROM book_audit_log LEFT JOIN JSON_TABLE( new_row_data, '$' COLUMNS ( title VARCHAR(255) PATH '$.title', author VARCHAR(255) PATH '$.author', price_in_cents INT(11) PATH '$.price_in_cents', publisher VARCHAR(255) PATH '$.publisher' ) ) AS r ON true WHERE book_audit_log.book_id = :bookId ORDER BY version_timestamp
So, when running the aforementioned SQL query, we now get the expected result set:
| version_timestamp | title | author | price_in_cents | publisher | |---------------------|-----------------------------------------------|---------------|----------------|-----------| | 2020-10-21 11:55:11 | High-Performance Java Persistence 1st edition | Vlad Mihalcea | 3990 | Amazon | | 2020-10-21 11:55:12 | High-Performance Java Persistence 1st edition | Vlad Mihalcea | 4499 | Amazon | | 2020-10-21 11:55:13 | | | | |
Awesome, right?
I'm running an online workshop on the 11th of October about High-Performance SQL.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 whenever we want to store non-structured data. For our audit log table, JSON columns work much better than using a strict relation model for the audit log info.
And, thanks to the MySQL JSON_TABLE function, we can always transform the JSON objects into a virtual relation table and benefit from all the SQL features provided by the underlying database to further transform the data.

You helped me a lot with your explanation. I was looking for the correct syntax and now I can make complex queries on my json columns.
I’m glad you found it useful. This is just a sample from my High-Performance SQL course. There are many more tips you can learn from that video course that you would love learning.