Imagine having a tool that can automatically detect JPA and Hibernate performance issues.
Hypersistence Optimizer is that tool!
In this article, I’m going to explain how the SQL Server OPENJSON function works and how it can help you transform a JSON object into a relational database table.
When using a relational database system, it’s a very good idea to design the database schema according to the relational model. However, there are specific situations when the relational model is way too strict, and that’s when a JSON column type might be very handy.
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 duplicating 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.
The Book table stores all the books we have in our library, and the BookAuditLog 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 BookAuditLog table contains the following data:
Transforming a JSON object to a relational table with the SQL Server OPENJSON 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, SQL Server provides us the OPENJSON function, which we can use to transform a JSON object to a relational database table, as illustrated by the following SQL query:
BookAuditLog.DmlTimestamp as VersionTimestamp,
title varchar(255) '$.Title',
author varchar(255) '$.Author',
price_in_cents bigint '$.PriceInCents',
publisher varchar(255) '$.Publisher'
) AS r
BookAuditLog.BookId = 1
When running the above SQL query, we get the following result set:
The OPENJSON function defines the virtual table column types and their associated JSON object properties where the column value will be extracted from.
The OPENJSON function resembles a CROSS JOIN. Therefore, we need the OUTER APPLY clause, which works just like a LATERAL JOIN, joining the BookAuditLog table rows with an underlying correlated subquery that extracts the virtual table records that are created by calling the OPENJSON function.
If you enjoyed this article, I bet you are going to love my upcoming 4-day Online Workshop!
JSON column types are very useful for storing non-structured data, like it’s the case of an audit log. Unlike a strict relation model, the JSON-based audit log doesn’t need any DDL modification if the audited tables are changing.
And, thanks to the SQL Server OPENJSON function, we can always transform the JSON objects into virtual relation tables and benefit from all the SQL features provided by SQL Server to further transform the data.