MySQL JSON_TABLE – Map a JSON object to a relational database table

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

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:

Tables used for the MySQL JSON_TABLE example that build a table from a JSON object

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-07-29 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-07-29 13:50:48 | Vlad Mihalcea  |
| 1       | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} |                                                                                                                                      | DELETE   | 2020-07-29 14:05:33 | 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’ 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?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Seize the deal! 40% discount. Seize the deal! 40% discount.

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.

Transactions and Concurrency Control eBook

One Comment on “MySQL JSON_TABLE – Map a JSON object to a relational database table

  1. Good news, if you know this syntax, then you know how to do it with tve Oracle database 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.