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.

Insert details about how the information is going to be processed

DOWNLOAD NOW