OPENJSON WITH Clause: Converting JSON Properties to Typed Values

An OPENJSON WITH clause turns JSON properties into columns with real SQL names and types. You choose each type, you point at each value with a path, and you decide whether a missing property is allowed. Without it, you get text and guesswork.

What you get without WITH

Say an API hands you a small order as JSON. You want the customer id, the date and the amount in proper columns. Let me start with OPENJSON alone and see what comes out.

DECLARE @j nvarchar(max) =
    N'{"customer":{"id":12},"orderDate":"2026-09-26T10:30:00","amount":19.25}';

SELECT [key], [value], [type]
FROM OPENJSON(@j);

You get one row per top-level property: customer, orderDate and amount. The value column is plain text for all of them. The type column is a number that says what JSON thought it was: 5 for the customer object, 1 for the date string and 2 for the amount. Nothing here is a SQL date or decimal yet.

Add the WITH clause

The WITH clause declares the shape you want. Each line has a column name, a SQL type and a path. Names need not match the JSON, because the path does the mapping. The path $.customer.id reaches inside the nested object.

The block below returns three results. The first is the typed row. The second is a strict path that finds nothing, caught so you can see the error number. The third is the same missing path in the default lax mode.

DECLARE @j nvarchar(max) =
    N'{"customer":{"id":12},"orderDate":"2026-09-26T10:30:00","amount":19.25}';

SELECT CustomerId, OrderDate, Amount, CustomerObject
FROM OPENJSON(@j)
WITH (CustomerId     int           'strict $.customer.id',
      OrderDate      datetime2(0)  '$.orderDate',
      Amount         decimal(19,4) '$.amount',
      CustomerObject nvarchar(max) '$.customer' AS JSON);

BEGIN TRY
    SELECT CustomerId FROM OPENJSON(N'{"amount":19.25}')
    WITH (CustomerId int 'strict $.customer.id');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number;
    PRINT ERROR_MESSAGE();
END CATCH;

SELECT CustomerId FROM OPENJSON(N'{"amount":19.25}')
WITH (CustomerId int '$.customer.id');

Read the results in order. The first row shows CustomerId 12, a real date, Amount 19.2500 and the customer object kept as JSON text. That AS JSON keeps a nested piece as JSON for later, instead of flattening it.

The strict path fails with error 13608, and the message says the property cannot be found on the specified path. The lax version returns NULL for the same missing value. Use strict for properties you require. Use lax for the optional ones.

When a value will not convert

Now a worse payload. The amount is the word abc. A typed column cannot hold that, so the whole query stops. One bad value in a big array can sink the entire load.

BEGIN TRY
    SELECT Amount FROM OPENJSON(N'{"amount":"abc"}')
    WITH (Amount decimal(19,4) '$.amount');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number;
END CATCH;

SELECT Amount AS amount_text, TRY_CONVERT(decimal(19,4), Amount) AS amount_number
FROM OPENJSON(N'{"amount":"abc"}')
WITH (Amount nvarchar(50) '$.amount');

The first query throws error 8114, a conversion error. The second reads the value as text, then TRY_CONVERT turns the bad one into NULL, so you can find and report it. When rejected values matter, load the risky fields as text first and convert afterward.

One row per order from an array

Real payloads are usually arrays. Point OPENJSON at the array and you get one row per element, with the same WITH clause.

DECLARE @orders nvarchar(max) =
    N'[{"customer":{"id":12},"amount":19.25},{"customer":{"id":13},"amount":5}]';

SELECT CustomerId, Amount
FROM OPENJSON(@orders)
WITH (CustomerId int           '$.customer.id',
      Amount     decimal(19,4) '$.amount')
ORDER BY CustomerId;

Two rows come back, customers 12 and 13, with amounts 19.2500 and 5.0000. The scale comes from the declared type, not from the JSON.

Conversion is not validation

A value that converts cleanly can still be wrong. An amount can be negative, a customer can be unknown. Do your checks in a staging table before the data reaches real tables, and keep the original payload as long as your retention rules allow.

Next time JSON lands in your lap, declare the types before you trust the values.

OPENJSON is not a validator, it is a typed doorway for JSON values.

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