How to Split JSON Value and Create Separate Fields in SQL Server

In some cases, you may need to split a JSON value and create separate fields in your data source based on the key names and values within the JSON object. This can be achieved using the JSON_VALUE function in SQL Server.

Prerequisites

• SQL Server with a table containing a JSON format column.

Steps

1. Create a table with a JSON format column. For example, a table named CustomerOrder with columns BusinessEntityID, FirstName, LastName, and OrderDetailsJson.
   
2. Create View using the following query to split the JSON value and create separate fields with the key names and values:

Create View SplitView As SELECT SJ.BusinessEntityID, SJ.FirstName, SJ.LastName,
       JSON_VALUE(x.Value, '$.SalesOrderID') as SalesOrderID,
       JSON_VALUE(x.Value, '$.OrderDate') as OrderDate,
       JSON_VALUE(x.Value, '$.TotalDue') as TotalDue
FROM SplitJson SJ
CROSS APPLY OPENJSON(OrderDetailsJson) as x

This query will create separate fields for SalesOrderID, OrderDate, and TotalDue based on the JSON values in the OrderDetailsJson column.

3. Connect the SQL connection to Bold BI and drag and drop the created view

Result

The result of the view will be a table with separate fields for each key name and value from the JSON object.

Additional References

• JSON_VALUE (Transact-SQL)
• OPENJSON (Transact-SQL)