MS SQL OpenJSON

In our increasingly data-driven world, JSON (JavaScript Object Notation) has emerged as a versatile and lightweight format for exchanging information. While JSON offers flexibility, working with it within the structured realm of relational databases like SQL Server can be cumbersome. Microsoft SQL Server has supported the OPENJSON function since SQL Server 2016 (Compatibility Level 130) with the primary purpose of converting JSON text into tabular format making it easier to query and manipulate JSON data to integrate into your current queries.

Syntax

Consider the following :

declare @json1 nvarchar(MAX)
set @json1 = '["Grocery", "Sport"]'

SELECT [c].[value] FROM OPENJSON(@json1) WITH ([value] nvarchar(max) '$') AS [c]

This returns a result set with one Column (called value) and two rows (“Grocery”, and “Sport”)

By Default the OPENJSON function returns three columns (key, value, and type) with one row for each key value pair in the JSON passed in, the use of the WITH clause (as above) enables the specification of columns and their datatype to be tabulated into the result.

Fighter parameter Sniffing and improve efficiency using OPENJSON

Parameter sniffing in SQL Server refers to a behavior where SQL Server generates and caches a query execution plan optimized for the specific parameter values provided during the first execution of a stored procedure or parameterized query. Consider developing an application for a shop, a customer may way to filter by categories of items, given that everything should be passed in as parameters if you wanted to filter you could generate SQL as follows and then increase the number of parameters inside of the IN clause.

If you wished to filter to products in the Grocery or Sport Categories you would need to Insert two parameters (one for each) lets call them @p0 and @p1

SELECT [Id]
      ,[Name]
      ,[Details]
      ,[Category]
      ,[Price]
  FROM [OpenJsonTest].[dbo].[Product]
  Where Category in (@p0, @p1)

The problem with the above is that not only can this become a problem is too many parameters are required but this may cause performance problems as it defeats query plan caching and may evict other cached query plans as this will be seen as a new query.

Using OpenJSON Function means that the query can remain the same and have a cached Query plan no matter how many parameters are required, the above query would require a single parameter (@json0) and look like this:

SELECT [Id]
      ,[Name]
      ,[Details]
      ,[Category]
      ,[Price]
  FROM [OpenJsonTest].[dbo].[Product]
  Where Category in (SELECT [c].[value] FROM OPENJSON(@json0) WITH ([value] nvarchar(max) '$') as [c])

Importing Data into SQL Server

OPENJSON can be used to easily import data into SQL Server as follows:

DECLARE @json NVARCHAR(max)  = N'{  
  "id" : 11,  
  "Name": "TV Remote",  
  "Details": "Universal Remote for TV",  
  "Category": "Electrical",  
  "Price": 55.23
  }';  
   
  INSERT INTO Product  
  SELECT *   
  FROM OPENJSON(@json)  
  WITH (id int,  
        [Name] nvarchar(255), [Details] nvarchar(max),   
        [Category] nvarchar(20), price decimal(18,2))

Why use OPENJSON

• Seamless Integration : When applications and services exchange JSON data with your SQL Server database, OPENJSON allows you to directly process and analyze this data without the need for complex external parsing or pre-processing steps.
• Enhanced Querying : OPENJSON lets you use standard SQL operations like SELECT, WHERE, JOIN, and others to target specific elements within your JSON data. This opens up a wide range of possibilities for filtering, extracting, and combining JSON data with your existing relational data.
• Flexibility : You can customize how OPENJSON interprets and presents the JSON data by providing an optional schema, giving you control over column names and data types in the resulting rowset.

Conclusion

OPENJSON provides a powerful and flexible tool to bridge the gap between JSON data and the structured world of SQL Server. By mastering this function, SQL developers can seamlessly integrate JSON data, enhance their analytical queries, and build more robust data-driven solutions within their database environment.

For more information on OPENJSON in T-SQL please click here