Shredding JSON Into Relational Tables in One Pass

Nested child rows need a reliable link to the parent identifiers generated during loading. Shredding JSON into staged rows lets SQL Server validate the document and preserve that relationship during one atomic load.

Define the Relational Contract Before Shredding JSON

The example accepts an array of orders with a source key, customer identifier, and lines array. Each line carries a product identifier and quantity. Those fields become typed relational columns rather than permanent strings containing unexplained numbers.

Run the complete example in a separate test database on SQL Server 2022 or later. SQL Server 2025 supports the same pattern. OPENJSON requires database compatibility level 130 or higher in the ordinary configuration used here.

The ARRAY argument to ISJSON requires SQL Server 2022 or later. It distinguishes the expected root shape from a valid JSON object. Do not silently lower database compatibility merely to match an old sample.

CREATE TABLE dbo.ImportOrder
(
    OrderId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    SourceKey nvarchar(30) NOT NULL UNIQUE,
    CustomerId int NOT NULL
);
CREATE TABLE dbo.ImportOrderLine
(
    OrderId int NOT NULL
        REFERENCES dbo.ImportOrder(OrderId),
    LineOrdinal int NOT NULL,
    ProductId int NOT NULL,
    Quantity int NOT NULL CHECK (Quantity > 0),
    PRIMARY KEY (OrderId, LineOrdinal)
);

The source key is stored on the parent and protected by a unique constraint. This gives the insert a reliable correlation value to return. Child order is represented explicitly instead of being inferred from physical insertion order.

Validate Syntax Before Parsing Fields

ISJSON validates JSON syntax and the requested root type. It does not validate required business fields, duplicate property names, or relationships with reference data. Application-level contract checks remain necessary before treating the document as an authorized order.

The following block starts the load and materializes parent fields once. Continue with the later blocks in the same SSMS session. Temporary tables preserve the staged rows across those blocks.

DECLARE @Payload nvarchar(max) = N'[
 {"source":"A100","customer":1,"lines":[
   {"product":10,"quantity":2},{"product":11,"quantity":1}]},
 {"source":"A101","customer":2,"lines":[
   {"product":12,"quantity":3}]}
]';
IF COALESCE(ISJSON(@Payload, ARRAY), 0) <> 1
    THROW 50001, 'Expected a valid JSON array.', 1;

DROP TABLE IF EXISTS #OrderRoot;
SELECT value, type INTO #OrderRoot FROM OPENJSON(@Payload);
IF EXISTS (SELECT 1 FROM #OrderRoot WHERE type <> 5)
    THROW 50008, 'Each order must be an object.', 1;

DROP TABLE IF EXISTS #ParentStage;
SELECT SourceKeyRaw, CustomerRaw, LinesJson
INTO #ParentStage
FROM #OrderRoot AS r
CROSS APPLY OPENJSON(r.value)
WITH
(
    SourceKeyRaw nvarchar(max) '$.source',
    CustomerRaw nvarchar(max) '$.customer',
    LinesJson nvarchar(max) '$.lines' AS JSON
);

IF NOT EXISTS (SELECT 1 FROM #ParentStage)
    THROW 50002, 'At least one order is required.', 1;

IF EXISTS
(
    SELECT 1 FROM #ParentStage
    WHERE SourceKeyRaw IS NULL
       OR LEN(LTRIM(RTRIM(SourceKeyRaw))) = 0
       OR DATALENGTH(SourceKeyRaw) > 60
       OR TRY_CONVERT(int, CustomerRaw) IS NULL
       OR TRY_CONVERT(int, CustomerRaw) <= 0
       OR COALESCE(ISJSON(LinesJson, ARRAY), 0) <> 1
)
    THROW 50003, 'Invalid order fields.', 1;

Using large strings during staging avoids truncating a key before validating its target length. The length check uses bytes appropriate to nvarchar(30). Numeric conversion is attempted safely, then checked against the positive identifier contract.

AS JSON preserves the nested lines array for its own extraction. Without that clause, a nested object or array does not become the desired JSON fragment. Missing required fields under lax paths need explicit rejection.

Expand Each Child Array Once When Shredding JSON

CROSS APPLY OPENJSON turns each preserved lines array into its child rows. The default array key supplies a zero-based ordinal. An explicit WITH clause then extracts the selected line fields.

IF EXISTS
(
    SELECT 1 FROM #ParentStage AS p
    CROSS APPLY OPENJSON(p.LinesJson) AS a
    WHERE a.type <> 5
)
    THROW 50009, 'Each line must be an object.', 1;

DROP TABLE IF EXISTS #LineStage;
SELECT CONVERT(nvarchar(30), p.SourceKeyRaw) AS SourceKey,
       CONVERT(int, a.[key]) AS LineOrdinal,
       d.ProductRaw,
       d.QuantityRaw
INTO #LineStage
FROM #ParentStage AS p
CROSS APPLY OPENJSON(p.LinesJson) AS a
CROSS APPLY OPENJSON(a.value)
WITH
(
    ProductRaw nvarchar(max) '$.product',
    QuantityRaw nvarchar(max) '$.quantity'
) AS d;

IF EXISTS
(
    SELECT 1 FROM #ParentStage
    GROUP BY CONVERT(nvarchar(30), SourceKeyRaw)
    HAVING COUNT_BIG(*) > 1
)
    THROW 50004, 'Duplicate source keys in the payload.', 1;

IF EXISTS
(
    SELECT 1 FROM #LineStage
    WHERE TRY_CONVERT(int, ProductRaw) IS NULL
       OR TRY_CONVERT(int, ProductRaw) <= 0
       OR TRY_CONVERT(int, QuantityRaw) IS NULL
       OR TRY_CONVERT(int, QuantityRaw) <= 0
)
    THROW 50005, 'Invalid line fields.', 1;

IF EXISTS
(
    SELECT 1 FROM #ParentStage AS p
    WHERE NOT EXISTS
    (
        SELECT 1 FROM #LineStage AS l
        WHERE l.SourceKey = CONVERT(nvarchar(30), p.SourceKeyRaw)
    )
)
    THROW 50006, 'Each order requires at least one line.', 1;

For shredding JSON, staging avoids parsing the full original document separately for every target operation. It does not promise that the engine physically reads every JSON byte only once. Validation and extraction still perform their own required work.

The shape checks reject non-object orders and lines before field extraction can discard them. Also reject duplicate property names and unexpected fields when that contract requires rejection. A syntactically valid document does not automatically satisfy a complete API schema.

I keep validation separate from the target inserts so failures describe the payload clearly. I also preserve a correlation key when generating relational identities. Assuming identities arrive in input order is a shortcut with an inconvenient destination.

Capture Parent Keys With OUTPUT

The parent insert returns both the new identity and the stored source key. That pair forms the mapping used by the child insert. Never match generated identities to children by row position in an OUTPUT result.

DROP TABLE IF EXISTS #OrderMap;
CREATE TABLE #OrderMap
(
    SourceKey nvarchar(30) NOT NULL PRIMARY KEY,
    OrderId int NOT NULL
);
SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
    THROW 50007, 'Run this standalone load outside a transaction.', 1;

BEGIN TRY
    BEGIN TRANSACTION;
    INSERT dbo.ImportOrder(SourceKey, CustomerId)
    OUTPUT inserted.SourceKey, inserted.OrderId
        INTO #OrderMap(SourceKey, OrderId)
    SELECT CONVERT(nvarchar(30), SourceKeyRaw),
           CONVERT(int, CustomerRaw)
    FROM #ParentStage;

    INSERT dbo.ImportOrderLine(OrderId, LineOrdinal, ProductId, Quantity)
    SELECT m.OrderId, l.LineOrdinal,
           CONVERT(int, l.ProductRaw), CONVERT(int, l.QuantityRaw)
    FROM #LineStage AS l
    JOIN #OrderMap AS m ON m.SourceKey = l.SourceKey;
    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    THROW;
END CATCH;

OUTPUT reflects modified target columns, so the correlation key is deliberately part of the parent table. Ordinary INSERT OUTPUT cannot simply expose any unrelated source alias. Designing that mapping removes the need for positional assumptions or identity-range arithmetic.

The mapping table has no foreign keys, check constraints, or enabled triggers. Those restrictions matter for an OUTPUT INTO destination. Its primary key also protects the one-source-key-to-one-parent relationship.

Define Retry and Reference-Data Behavior

The unique source key rejects a repeated load containing the same orders. When I ran the load twice, the second run failed on that key and left the tables unchanged. That is deliberate duplicate detection, rather than a complete idempotent replay design. Decide whether an exact replay should succeed, be ignored, or return a prior result.

A replay with changed contents needs a separate business rule. Do not treat every duplicate-key error as proof of a harmless retry. Compare the authorized source identity and payload meaning before accepting an existing parent.

The sample identifiers are standalone demonstration values. Production loading must validate customer and product references or rely on appropriate trusted foreign keys. Include the reference checks inside a consistent transaction boundary when concurrent changes matter.

Reject fractional quantities, out-of-range numbers, and unsupported empty arrays according to the documented contract. This example requires positive integers and at least one line. A different business contract needs different validation rather than silent coercion.

Reconcile the Loaded Relationships

Can every inserted line be traced to its original source order? Inspect the mapping and joined target rows after the successful commit. Count staged and inserted parents and children within the same load scope.

SELECT o.SourceKey, o.OrderId, o.CustomerId,
       l.LineOrdinal, l.ProductId, l.Quantity
FROM dbo.ImportOrder AS o
JOIN #OrderMap AS m ON m.OrderId = o.OrderId
JOIN dbo.ImportOrderLine AS l ON l.OrderId = o.OrderId
ORDER BY o.SourceKey, l.LineOrdinal;

Test malformed JSON, missing fields, duplicate keys, invalid quantities, and child-insert failures. Confirm that target parents do not remain committed after a failed child insert. Keep raw rejected payloads only under the approved data-handling policy.

Keep a versioned payload contract beside the database procedure. Property paths are case-sensitive in OPENJSON matching, even when ordinary database text comparisons are case-insensitive. A producer changing customer to Customer has changed the incoming contract.

Bound document size and expected parent and child counts before accepting a production request. Large arrays can consume memory, temporary storage, and transaction-log capacity. Split oversized submissions through an explicit batch contract rather than truncating their contents.

Decide whether an empty root array represents a valid no-op or an invalid request. This example rejects it, along with parents lacking lines. Explain those rules to the producer so a validation error leads to correction instead of repeated retries.

Store load identity and reconciliation counts when auditing is required. Commit that audit record consistently with the target changes. Logging success before the transaction commits can leave a convincing record of work that never became durable.

Shredding JSON succeeds when syntax, business validation, generated keys, and transaction behavior agree. Preserve all four parts when adapting the example. A shorter insert is useful only if it preserves the actual relationships.

Related reading on this blog: SQL SERVER Performance: JSON vs XML and 2016: Check Value as JSON With ISJSON().

JSON parsing is not a relational load, it is the first step in validating and preserving relational meaning.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.