SQL Server OPENJSON – Map JSON to a relational 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 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.
Database tables
We are going to reuse the same database tables we used when implementing an audit log table using SQL Server triggers and JSON columns:
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:
| BookId | OldRowData | NewRowData | DmlType | DmlTimestamp | DmlCreatedBy | |--------|------------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------|---------|-------------------------|---------------| | 1 | | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":3990,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | INSERT | 2021-02-18 11:40:28.343 | Vlad Mihalcea | | 1 | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":3990,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":4499,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | UPDATE | 2021-02-18 11:43:22.803 | Vlad Mihalcea | | 1 | {"Id":1,"Author":"Vlad Mihalcea","PriceInCents":4499,"Publisher":"Amazon","Title":"High-Performance Java Persistence 1st edition"} | | DELETE | 2021-02-18 11:44:25.630 | Vlad Mihalcea |
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:
SELECT BookAuditLog.DmlTimestamp as VersionTimestamp, r.* FROM BookAuditLog OUTER APPLY OPENJSON ( JSON_QUERY( NewRowData, '$' ) ) WITH ( title varchar(255) '$.Title', author varchar(255) '$.Author', price_in_cents bigint '$.PriceInCents', publisher varchar(255) '$.Publisher' ) AS r WHERE BookAuditLog.BookId = 1 ORDER BY VersionTimestamp
When running the above SQL query, we get the following result set:
| VersionTimestamp | Title | Author | PriceInCents | Publisher | |-------------------------|-----------------------------------------------|---------------|--------------|-----------| | 2021-02-18 11:40:28.343 | High-Performance Java Persistence 1st edition | Vlad Mihalcea | 3990 | Amazon | | 2021-02-18 11:43:22.803 | High-Performance Java Persistence 1st edition | Vlad Mihalcea | 4499 | Amazon | | 2021-02-18 11:44:25.630 | | | | |
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.
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 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.
