Free SQL Help for Devs, DBAs, and the Curious

With the increasing popularity of JSON (JavaScript Object Notation) as a data interchange format, SQL Server 2016 introduced built-in support for JSON to efficiently deal with JSON data. One of these features is OPENJSON, a table-valued function that parses JSON text and allows you to shred it into a tabular format. Today, we will be discussing how to use OPENJSON to shred JSON data in SQL Server 2016 and onwards.

To shred JSON data using OPENJSON, you need to pass the JSON text and the path expression to the OPENJSON function. To illustrate, let's consider the following JSON object:

DECLARE @json NVARCHAR(4000) = N'[  
   {  
      "id":1,
      "name":"John Doe",
      "skills":["SQL","DB2","Azure"]
   },
   {  
      "id":2,
      "name":"Jane Doe",
      "skills":["MySQL","SQL Server 2017"]
   }
]';

To shred this JSON object into a relational format, we pass the JSON text and the relative path of each property in the JSON object to the OPENJSON function like so:

SELECT *
FROM OPENJSON(@json)
WITH (
   id int '$.id',
   name nvarchar(100) '$.name',
   skills nvarchar(max) '$.skills' AS JSON
);

The result is a tabular representation of the JSON data. In the above query, the '$' represents the root of the JSON object, and the property names (id, name, skills) are relative path expressions.

OPENJSON also supports shredding nested arrays. For instance, if we want to shred the 'skills' array in the above JSON object, we can do so by using the CROSS APPLY operator with OPENJSON like so:

SELECT id, name, skill
FROM OPENJSON(@json)
WITH (
   id int '$.id',
   name nvarchar(100) '$.name',
   skills nvarchar(max) '$.skills' AS JSON
) 
CROSS APPLY OPENJSON(skills)
WITH (
   skill nvarchar(100) '$'
);

This will result in a tabular representation of each skill for each person. This ability to shred JSON arrays makes OPENJSON a powerful tool for dealing with complex JSON data.

In conclusion, OPENJSON provides a simple and efficient way to shred JSON data into a tabular format in SQL Server 2016 and onwards. Its ability to deal with complex JSON data, including nested arrays, makes it a powerful tool for dealing with JSON data in a SQL Server environment. Remember that OPENJSON is available in SQL Server 2016 and onwards, and is not available in other database systems like MySQL 5.7 or DB2 11.1.