Rajeev Pentyala – Microsoft Power Platform

In this article, lets see how to work with JSON data in SQL Server using SSMS. Following features will be covered in this article.

• Format SQL table data in JSON format (Using FOR JSON PATH clause)
• Store JSON data in SQL table
• Query JSON data in SQL server (Using JSON_VALUE and JSON_QUERY functions)
• Update JSON data in SQL table (Using JSON_MODIFY function)
• Convert JSON data in to SQL table format (Using OPENJSON function)
• Validate JSON data format (Using ISJSON function)

Format SQL table data in JSON format:

Lets see how to format SQL query tabular results in JSON format using FOR JSON clause.

• Create a simple ‘Employee’ table in SQL server.
• When you query ‘Employee’ table you would get the result in tabular format.

• Lets convert the result set in to JSON format using FOR JSON clause.

-- JSON PATH
select *
from Employee
FOR JSON PATH

• Use WITHOUT_ARRAY_WRAPPER to get a single JSON object instead of an array. Use this option if the result of query is single object.

-- JSON PATH and WITHOUT_ARRAY_WRAPPER
select *
from Employee
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

• We can also form inline JSON object format. Lets see how to show ‘Address’ as a separate object.

-- JSON PATH, WITHOUT_ARRAY_WRAPPER and 'Address' as seperate object
select
ID,Name,
Street1 as [Address.Street1], Street2 as [Address.Street2],City as [Address.City], ZipCode as [Address.ZipCode]
from Employee
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Store JSON data in SQL table:

• JSON is a textual format that can be used like any other string type in SQL Database. JSON data can be stored as a standard NVARCHAR.
• In the example below SQL table has ‘JSONData’ column which is NVARCHAR(MAX).

Query JSON data in SQL server:

• Lets query the JSON data using JSON_VALUE and JSON_QUERY.
• JSON_VALUE:
  • JSON_VALUE function extracts a value from JSON text stored in the SQL column.
  • The extracted value can be used in any part of SQL query.
• JSON_QUERY:
  • JSON_QUERY function extracts complex sub-object such as arrays or objects that are placed in JSON text.
  • Example ‘Tags’ column in above SQL Table.

Update JSON data in SQL table:

• JSON_MODIFY function can be used to update the JSON text without re-parsing the entire structure.
• Below is the query to update the ‘Price’ of all ‘Yellow’ color products to 1000.

Update Products
set JSONData = JSON_MODIFY(JSONData,'$.Price',1000)
where
JSON_VALUE(JSONData,'$.Color') = 'Yellow'

• Post query execution, ‘Price’ updated to 1000 as shown below.

Convert JSON data in to SQL table format

• Using OPENJSON function JSON data can be transformed in to SQL table format.
• OPENJSON
  • Its a table-value function that parses JSON text, locates an array of JSON objects, iterates through the elements of the array, and returns one row in the output result for each element of the array.

Validate JSON data format

• Since JSON is stored in a standard text, there are no guarantees that the values stored in text columns are properly formatted using ISJSON function.
• ISJSON function returns the value 1 if the data properly formatted in JSON.

ISJSON(SQL_Cell) > 0

🙂