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.

Insert details about how the information is going to be processed

DOWNLOAD NOW