How to import JSON into SQL Server (and the parts OPENJSON leaves to you)

Getting a JSON file into SQL Server takes ten lines of T-SQL. Getting all of it in, into tables a report can join, is the part every tutorial stops just before.

The native way: OPENJSON

Two steps: read the file into one string, then shred the string into rows. Here is a file of orders, each with a customer object and an array of lines:

{ "orders": [
  { "id": 1001, "placed": "2026-03-14T09:30:00Z",
    "customer": { "number": "C-4471", "city": "Frankfurt" },
    "lines": [ { "sku": "A-7", "qty": 2, "price": 19.90 },
               { "sku": "B-2", "qty": 1, "price": 4.50 } ] }
] }

And the T-SQL that turns the orders into a table:

DECLARE @json NVARCHAR(MAX);

SELECT @json = BulkColumn
FROM OPENROWSET(BULK 'C:\data\orders.json', SINGLE_CLOB) AS f;

INSERT INTO dbo.Orders (OrderId, PlacedAt, CustomerNumber, City)
SELECT OrderId, PlacedAt, CustomerNumber, City
FROM OPENJSON(@json, '$.orders')
WITH (
    OrderId         INT            '$.id',
    PlacedAt        DATETIME2(0)   '$.placed',
    CustomerNumber  NVARCHAR(20)   '$.customer.number',
    City            NVARCHAR(100)  '$.customer.city'
);

The lines belong in a table of their own, each row carrying the order it came from. CROSS APPLY a second OPENJSON over the array, declared AS JSON so it arrives as a fragment rather than as NULL:

INSERT INTO dbo.OrderLines (OrderId, Sku, Qty, Price)
SELECT o.OrderId, l.Sku, l.Qty, l.Price
FROM OPENJSON(@json, '$.orders')
     WITH (OrderId INT '$.id', Lines NVARCHAR(MAX) '$.lines' AS JSON) AS o
CROSS APPLY OPENJSON(o.Lines)
     WITH (Sku NVARCHAR(20) '$.sku', Qty INT '$.qty', Price DECIMAL(10,2) '$.price') AS l;

Four things that catch everybody the first time:

• The path is on the SQL Server machine, not on yours. C:\data\orders.json means the server’s C: drive, and the service account needs read access to it — plus your login needs ADMINISTER BULK OPERATIONS. On Azure SQL Database there is no server drive at all; the file has to come from blob storage.
• The database must be at compatibility level 130 or higher, or OPENJSON is not recognised — even on a 2019 or 2022 server, if the database was restored from an older one.
• JSON paths are case-sensitive. '$.Customer' does not find "customer", and in the default lax mode it returns NULL rather than an error. Write 'strict $.customer' while developing and a wrong path fails loudly instead.
• Encoding. SINGLE_CLOB reads the file as VARCHAR in the database’s code page, so a UTF-8 file containing ü or ß can arrive mangled; SINGLE_NCLOB expects UTF-16. Check one row with an umlaut in it before trusting the rest.

Where OPENJSON stops being enough

Everything above works. What it does not do is decide anything: every path and every type in that WITH clause is a guess you typed. On a real file, five of those guesses go wrong quietly.

1. A field you did not list is not imported, and nothing says so

JSON records are allowed to differ. If record 551 is the first to carry a discount field and you wrote the WITH clause from the first record, the discount is simply not there — no error, no warning, no NULL column to notice. The column list has to come from every record, which means reading the whole file before deciding the shape.

2. Exploding an array duplicates its parent

Shredding orders and lines into one result with CROSS APPLY gives one row per line, with the order’s columns repeated on each. Sum an order-level amount over that and every order with three lines counts three times. An array of objects is a second table, not a wider first one — which is why the example above inserts lines separately, carrying only the key.

3. Dates are strings, and some carry an offset

JSON has no date type. "2026-03-14T09:30:00+01:00" is text until you say otherwise, and DATETIME2 has nowhere to keep the +01:00. Either convert to UTC on the way in and record that you did, or use DATETIMEOFFSET. What you must not do is drop the offset silently, which is what a plain DATETIME2 conversion of a mixed file does to an hour of every day.

4. One field, two types

"id": 7 in most records and "id": "A-7" in a few. Typed INT, the few fail the whole insert — or, with TRY_CAST, become NULL without comment. Count what each field actually holds before choosing its type: “number in 493, text in 7” is a decision, and it should be yours.

5. Arrays of plain values

"tags": ["b2b", "priority"] is not a set of rows worth a table and does not fit in one cell either. There are five honest things to do with it — keep it as JSON text, take the first value, join the values with a separator, count them, or skip it — and four of the five throw something away. Keeping the JSON text is the only default that loses nothing.

Flattening rules worth agreeing on before you start

However the import is done — by hand, in a script, or with a tool — it is making these decisions. Better to make them on purpose:

That fifth row is a small thing with a large effect. If the lines table calls its key order_id, anything that later infers relationships by column name — a modelling tool, a BI tool’s auto-detect, a colleague — links the two tables by itself. Call it id and it collides with the child’s own key and links to nothing.

Choosing SQL Server types for JSON values

SQL Server 2025 and Azure SQL add a native json type, which stores a document more compactly and validates it on the way in. It is a good home for the leftovers in the last row; it does not change the advice for everything above it, because a report still wants columns.

JSON Lines (.jsonl, .ndjson)

One JSON object per line, no surrounding array — the format most APIs and log exports use. OPENJSON expects one document, so split the file on line breaks first:

SELECT j.*
FROM STRING_SPLIT(@json, CHAR(10)) AS line
CROSS APPLY OPENJSON(line.value)
     WITH (Id INT '$.id', Event NVARCHAR(50) '$.event', At DATETIME2 '$.at') AS j
WHERE ISJSON(line.value) = 1;

The ISJSON filter skips the empty last line and any CHAR(13) leftovers from Windows line endings. For a file of millions of lines this is slow — it is one string in memory — and the honest answer is to load it in batches from outside SQL Server.

Doing it with Netune

Questions people ask