Shred JSON Data into Rows and Columns with T-SQL

Problem

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.

Solution

In this article, 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.

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:

/* MSSQLTips.com */
{
  "OrderId": "0001",
  "Customer": { "Name": "Amanda" },
  "Items": [
    { "Name": "Heated Blanket", "Qty": 1 },
    { "Name": "Glove Warmers", "Qty": 2 }
  ]
}

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:

/* MSSQLTips.com */
DECLARE @JSON nvarchar(MAX)
    = N'{
  "OrderId": 1,
  "Customer": { "Name": "Amanda" },
  "Items": [
    { "Name": "Heated Blanket", "Qty": 1 },
    { "Name": "Glove Warmers", "Qty": 2 }
  ]
}';
SELECT
    [key],
    [value],
    [type]
FROM
    OPENJSON(@JSON);
GO

FROM OPENJSON(@JSON); GO

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:

/* MSSQLTips.com */
DECLARE @JSON nvarchar(MAX)
    = N'{
  "OrderId": 1,
  "Customer": { "Name": "Amanda" },
  "Items": [
    { "Name": "Heated Blanket", "Qty": 1 },
    { "Name": "Glove Warmers", "Qty": 2 }
  ]
}';
SELECT
    OrderId,
    CustomerName,
    Items
FROM
    OPENJSON(@JSON)
        WITH
        (
            OrderId int '$.OrderId',
            CustomerName nvarchar(100) '$.Customer.Name',
            Items nvarchar(MAX) '$.Items' AS JSON
        );
GO

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:

/* MSSQLTips.com */
DECLARE @JSON NVARCHAR(MAX) =
N'{
  "OrderId": 1,
  "Customer": {
    "Name": "Amanda"
  },
  "Items": [
    {
      "Name": "Heated Blanket",
      "Quantity": 1
    },
    {
      "Name": "Glove Warmers",
      "Quantity": 2
    }
  ]
}';
SELECT Orders.OrderId,
       Orders.CustomerName,
       Items.ItemName,
       Items.Quantity
FROM OPENJSON(@JSON)
WITH (
    OrderId INT '$.OrderId',
    CustomerName NVARCHAR(256) '$.Customer.Name',
    Items NVARCHAR(MAX) '$.Items' AS JSON
    ) AS Orders
CROSS APPLY OPENJSON(Orders.Items)
WITH (
    ItemName NVARCHAR(256) '$.Name',
    Quantity int '$.Quantity'
) AS Items;    
GO

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.

/* MSSQLTips.com */
DECLARE @JSON nvarchar(MAX)
    = N'{
  "OrderId": 1,
  "Customer": { "Name": "Amanda" },
  "Items": [
    { "ItemId" : 1, "Name": "Heated Blanket", "Qty": 1 },
    { "ItemId" : 2, "Name": "Glove Warmers", "Qty": 2 }
  ]
}';
/* Results Set 1 */
SELECT
    OrderId,
    CustomerName
FROM
    OPENJSON(@JSON)
        WITH
        (
            OrderId int '$.OrderId',
            CustomerName nvarchar(100) '$.Customer.Name'
        ) AS Orders;
/* Results Set 2 */
SELECT
    Orders.OrderId,
    Items.ItemId,
    Items.ItemName,
    Items.Qty
FROM
    OPENJSON(@JSON)
        WITH
        (
            OrderId int '$.OrderId',
            Items nvarchar(MAX) '$.Items' AS JSON
        )             AS Orders
    CROSS APPLY OPENJSON(Orders.Items)
                    WITH
                    (
                        ItemId int '$.ItemId',
                        ItemName nvarchar(256) '$.Name',
                        Qty int '$.Qty'
                    ) AS Items;
GO

I’ve included the results below:

Something to keep in mind about CROSS APPLY is that if the array is missing items or empty, the parent rows will be dropped.

/* MSSQLTips.com */
DECLARE @JSON nvarchar(MAX)
    = N'{
  "OrderId": 1,
  "Customer": { "Name": "Amanda" },
  "Items": []
}';
SELECT
    Orders.OrderId,
    Orders.CustomerName,
    Items.ItemName,
    Items.Qty
FROM
    OPENJSON(@JSON)
        WITH
        (
            OrderId int '$.OrderId',
            CustomerName nvarchar(256) '$.Customer.Name',
            Items nvarchar(MAX) '$.Items' AS JSON
        )             AS Orders
    CROSS APPLY OPENJSON(Orders.Items)
                    WITH
                    (
                        ItemName nvarchar(256) '$.Name',
                        Qty int '$.Qty'
                    ) AS Items;
GO

Empty results:

Verify With ISJSON

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.

/* MSSQLTips.com */
DECLARE @JSON nvarchar(MAX)
    = N'{
  "OrderId": 1,
  "Customer": { "Name": "Amanda" },
  "Items": [
    { "ItemId": 1, "Name": "Heated Blanket", "Qty": 1 },
    { "ItemId": 2, "Name": "Glove Warmers", "Qty": 2 }
  ]
}';
SELECT
    ISJSON(@JSON) AS ValidJson;
GO

Results:

Conclusion

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.

Next Steps

• Koen Verbeeck has been busy writing articles about the new regex functions in SQL Server 2025. You can check out one of my favorites, “Replace Text using RegEx with SQL Server 2025 REGEXP_REPLACE”.
• Hristo Hristov wrote an extremely detailed article about extracting JSON and converting to XML in the article, “JSON Data Extract from SQL Server 2025.”
• For a detailed overview of the ISJSON function, check out “SQL Server ISJSON for valid Value, Array or Object JSON format” by Daniel Calbimonte.

Jared Westover

Jared Westover is a SQL Server specialist with two decades of industry experience covering T-SQL development, performance tuning, administration and Microsoft Fabric. He is currently a software architect at Crowe, an author at Pluralsight and primary contributor at sqlhabits.com.  On MSSQLTips.com, Jared is a respected award-winning author for his clever T-SQL solutions and bringing to light new real-world solutions to age-old development problems.

• MSSQLTips Awards
  • Achiever Award (75+ tips) – 2026
  • Author of the Year – 2023
  • Author Contender – 2024/2025