How to Join 2 Tables with JSON Data in SQL Server

Learn how to effectively join two tables in SQL Server when one has JSON data that needs to be parsed, ensuring a seamless integration and readable output.
---
This video is based on the question https://stackoverflow.com/q/66815296/ asked by the user 'John McDonnell' ( https://stackoverflow.com/u/612072/ ) and on the answer https://stackoverflow.com/a/66816744/ provided by the user 'iamdave' ( https://stackoverflow.com/u/1350987/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to join 2 tables, where data in one table has JSON data that needs to be parsed?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Join 2 Tables with JSON Data in SQL Server

In the world of databases, it’s common to encounter scenarios where you need to join tables that contain various data formats. One of the challenges that can arise is when one of these tables contains JSON data. Joining such tables can seem daunting, but with the right approach, it can be manageable. Here, we will guide you through the process of joining two tables in SQL Server, where one of the tables features JSON data.

Understanding the Problem

In this scenario, you have two tables: Order and History. The Order table contains standard order details, while the History table stores change history in JSON format. You want to generate a comprehensive output that combines data from both tables in a structured manner, including breaking down the JSON data into individual records.

Table Structure Overview

Order Table

Order_Number

Delivery_Date

History Table

Order_Number

History_Details (stored as JSON)

Expected Output

The desired output structure includes:

Order Number

Delivery Date

FieldName

ValueBefore

ValueAfter

The Solution

To achieve the desired output, we need to write a SQL query that joins these two tables while parsing the JSON data. Below are the steps to do this effectively.

Step 1: Creating Sample Data

For the sake of this explanation, let’s set up our sample data within temporary tables:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Writing the Query

Now we can construct a query that retrieves the necessary details from both tables and parses the JSON data using the OPENJSON function:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Analyzing the Output

When the above query is executed, it will produce a result set where each order is listed alongside its delivery date and the corresponding historical changes. The JSON fields are parsed into rows, which can be specifically used in reporting or querying later.
Conclusion

While dealing with JSON data in SQL, especially in conjunction with relational data, may seem complex at first, employing methods like OPENJSON and carefully structuring your joins can simplify the process greatly. This approach can be invaluable for effectively managing change logs and history tables in your SQL databases.

By following the structured query we outlined above, you can seamlessly join tables and gain useful insights from your data.

Feel free to try this out on your datasets and adapt it as needed!

Видео How to Join 2 Tables with JSON Data in SQL Server канала vlogize