Use OPENJSON with a WITH schema and CROSS APPLY to convert JSON objects and nested arrays into relational rows and columns; validate with ISJSON, set database compatibility level ≥130, and add computed/indexed columns for performance.
Quick recipe (what to run)
Validate JSON: use ISJSON() before shredding.
Parse root object: CROSS APPLY OPENJSON(... ) WITH (...) to map properties to typed columns.
Shred arrays: CROSS APPLY OPENJSON(rootJson, '$.ArrayName') to return rows, then OPENJSON(... ) WITH (...) to extract fields from each array element.
Example: order JSON → rows and columns
sql
DECLARE @j NVARCHAR(MAX) = N'
{
"OrderId":"0001",
"Customer":{"Name":"Amanda","Email":"a@example.com"},
"Items":[
{"Name":"Heated Blanket","Qty":1,"Price":49.99},
{"Name":"Glove Warmers","Qty":2,"Price":9.99}
],
"OrderDate":"2026-05-10"
}';
SELECT
r.OrderId,
r.OrderDate,
c.Name AS CustomerName,
c.Email AS CustomerEmail,
i.ItemName,
i.Qty,
i.Price
FROM (SELECT @j AS json) AS src
CROSS APPLY OPENJSON(src.json) WITH (
OrderId varchar(20) '$.OrderId',
OrderDate date '$.OrderDate',
Customer nvarchar(max) '$.Customer' AS JSON,
Items nvarchar(max) '$.Items' AS JSON
) AS r
CROSS APPLY OPENJSON(r.Customer) WITH (Name varchar(100) '$.Name', Email varchar(200) '$.Email') AS c
CROSS APPLY OPENJSON(r.Items) AS items -- returns key,value,type for each array element
CROSS APPLY OPENJSON(items.value) WITH (ItemName varchar(200) '$.Name', Qty int '$.Qty', Price decimal(10,2) '$.Price') AS i;
What this does: maps root fields, extracts nested Customer object, then expands Items array into one row per item.
Comparison of common JSON shredding approaches
Method
Best for
Output shape
Notes
OPENJSON (default)
Quick key/value inspection
key; value; type
Good for ad-hoc exploration.
OPENJSON WITH
Typed column mapping
Relational columns
Use for predictable schema and types.
CROSS APPLY + OPENJSON(array)
Nested arrays → rows
One row per array element
Essential for shredding arrays into rows.
JSON_VALUE / JSON_QUERY
Single scalar or fragment extraction
Scalar or JSON fragment
Use for simple lookups; less efficient for many rows.
Most databases I see nowadays have at least one column that stores JSON objects as NVARCHAR(MAX). If you look hard enough, I bet you have one. How do you convert JSON objects with arrays into a structured format of columns and rows? Not long ago, a developer asked me that exact question. It’s an important question given how rampant JSON is as a data exchange format, especially for web APIs.
We’ll look at a technique called JSON shredding, which converts semi-structured objects into columns and rows. First, I want to look at why working with JSON has become a required skill for any database professional. Next, we’ll use a built-in table-valued function called OPENJSON. Then we’ll extend it using the WITH clause and CROSS APPLY to access nested arrays. By the end of this article, you’ll have the skills to shred JSON with ease and a helpful resource to bookmark for later.
Key Takeaways
JSON shredding is essential for converting JSON objects into structured rows and columns in databases.
Use OPENJSON to parse JSON data easily; it requires SQL Server 2016 or higher to function properly.
The WITH clause helps specify a schema for mapping JSON properties to SQL columns with correct data types.
CROSS APPLY allows shredding nested arrays into rows, enhancing data retrieval from JSON structures.
Verify JSON validity with ISJSON to prevent errors in processing and ensure successful shredding.
JSON Explored
It’s hard to believe that in our modern era, a data professional hasn’t worked with or used JSON in some form. However, just in case, here is a quick summary. JSON (JavaScript Object Notation) is a light, text-based data format usually used to exchange data between APIs. JSON consists of objects, arrays, key-value pairs, and scalar values. My favorite feature is that it’s also easy for humans to read.
Here is a simple example of a JSON object containing an array:
The example above is a wish list my wife put together, consisting of one JSON object with three properties: OrderId, Customer, and Items. The Customer property is another JSON object with a Name property. The Items property is a JSON array of two objects, each with a Name and a Quantity property. If you can’t tell, she likes staying warm.
JSON and SQL Server
Before JSON, there was XML (Extensible Markup Language). Many years ago, I supported an application that required shredding XML documents into rows and columns for business reports. I suppose this is where the term shredding came from. At least the first mention I can find of it involves XML. I was never a big fan of XML because it’s hard to read and work with. If XML is your jam, then keep on rocking it. In my experience and others’, JSON is easier to read and is the gold standard for API data exchange. Starting with SQL Server 2016, Microsoft added built-in JSON support.
Before SQL Server 2025, JSON data was usually stored in an NVARCHAR(MAX) column. If you had tight control over the size, you could use a variable-size NVARCHAR(4000). Starting with SQL Server 2025, there is a native JSON data type, which I hope to write about in a future article.
Shredding JSON with OPENJSON
Before OPENJSON, I think data professionals used a combination of CHARINDEX and SUBSTRING to parse JSON. I’m sure there are examples of these techniques online. But those days are over. OPENJSON is a table-valued function that parses JSON and returns a rowset. Let’s take a look at how to use it. Keep in mind that you need to be on at least SQL Server 2016, and that your database compatibility level is set to 130 or higher. If you’re not at least on 2016, consider upgrading.
Below is an example of shredding the JSON object shown earlier without the array. Notice that we can use the OPENJSON function without a WITH clause to get the key-value pairs:
After running the query above, the results look like this:
The type column indicates the value’s type. Here are the possible types:
0 – NULL
1 – String
2 – Number
3 – True/False
4 – Array
5 – Object
That’s not exactly what I’m looking for, but it’s a start. I want the data in columns and rows. We can accomplish that using the WITH clause.
Using the WITH Clause
When you think of the WITH clause, the popular Common Table Expressions (CTEs) might come to mind. In OPENJSON, the WITH clause lets you specify an explicit schema that maps JSON property names to typed SQL columns with appropriate data types. Here is an example of using the WITH clause to shred the JSON object into columns and rows:
The $. notation is a JSON path that allows you to navigate to a specific property in a JSON object. After running the query above, the results look like this:
Notice how we mapped the JSON properties to SQL columns using the WITH clause. But the Items column is still a JSON array. To shred that into rows, we can use CROSS APPLY with another OPENJSON call.
USING CROSS APPLY to Shred Nested Arrays
Both types of APPLY operators (CROSS and OUTER) are the Swiss Army knife of SQL, with more uses than a roll of duct tape. They let you apply an expression to each row in an outer table or expression. CROSS APPLY behaves like an INNER JOIN, returning only rows that exist in both expressions.
An everyday use case is returning rows from a table-valued function for each row in an outer query. Remember that OPENJSON is a table-valued function (TVF). In SQL Server, you can use a TVF as a table source in FROM, JOIN, or APPLY, but it returns a table rather than a single value. I’ve included below how to use CROSS APPLY with OPENJSON to shred the Items array into rows:
After running the query above, the results look like this:
Now I want to take this one step further by placing the items in a separate table or result set in our example. Notice that I added the ItemId property to the array.
The last function I want to mention is ISJSON. This function lets you test whether a string contains valid JSON, helping reduce parsing errors. Starting with SQL Server 2022, you can also test for specific JSON types, including VALUE, ARRAY, OBJECT, and SCALAR. We can’t shred it if it’s not valid. You can even use it in a CASE expression. The example below keeps it simple with a SELECT.
For this article, I wanted to answer the question: How do you shred JSON into rows and columns in SQL Server? The answer is to use the OPENJSON table-valued function using the WITH clause and CROSS APPLY to shred nested arrays.
If you don’t use a WITH clause, you’ll get key-value pairs as rows, which might not be what you want. Also, when we use the WITH clause, it can map JSON properties to SQL columns with the right data types. Finally, by using CROSS APPLY with another OPENJSON call, you can shred nested arrays into rows.
Understanding TLS and Its Role in Database Security Transport Layer Security (TLS) is the cornerstone of secure data transmission over the internet. It safeguards data integrity and confidentiality during communication between clients and servers. In the context of Azure SQL Database, TLS plays a vital role in encrypting and securing connections, thereby thwarting potential eavesdropping and data tempering attempts. The client_tls_version_n Field in Azure SQL Auditing The field records the version of the TLS protocol used by client when establishing a connection to the Azure SQL Database. Monitoring this field is crucial for several reasons: 1. Ensuring Compliance with Security Standards : Many industries mandate the use of specific TLS versions to meet security guidelines. The client_tls_version_n field helps verify compliance with these standards. 2. Identifying Outdated TLS Versions : Older TLS versions, like TLS 1,0 and 1.1, are considered less secure. This field helps ...
Problem My SQL Server query is running slowly. The WHERE clause is comparing against a column that is indexed, but the SQL query isn't doing an index seek. It sure seems like it shouldn't be scanning. Why might that be? Solution This is a common problem, especially for newer TSQL developers that are less familiar with the inner working of the SQL Server query engine. This tip will look at 2 different queries where indexes aren't used by the optimizer, but could be if the query was tweaked. Next, it will explain why this small coding change will work. Finally, we will perform the tweak to see what sort of performance improvement can be expected. Glossary of Terms This tutorial will use 2 terms that might be new to readers. Argument – An argument is another name for a Boolean expression that may appear in a join predicate, WHERE clause, or HAVING clause. Each of these clauses may have many arguments combined with AND and OR keywords. An argument might be "OrderDate = ...
Comments
Post a Comment