ISJSON Type Constraints: Validate the Top-Level Value

I use ISJSON type constraints to state the top-level shape a caller must supply. Valid JSON isn’t always an object. SQL Server 2022 adds explicit choices for that distinction.

Compare the default with VALUE

Without a type constraint, ISJSON accepts valid objects or arrays. The first two rows meet that default container rule. VALUE also accepts both of them, but its contract includes other valid JSON values.

The third and fourth rows are a number and a quoted string. Both pass VALUE while failing the default container check. I keep the checks beside each other so a change in the validator’s contract remains visible during review.

WITH Inputs AS
(
 SELECT CaseId, CAST(JsonText AS nvarchar(200)) AS JsonText
 FROM (VALUES (1,N'{}'),(2,N'[]'),(3,N'12'),(4,N'"blue"'),
              (5,N'true'),(6,N'null'),(7,N'{"x":}'),
              (8,CAST(NULL AS nvarchar(200)))) v(CaseId,JsonText)
)
SELECT CaseId, JsonText, ISJSON(JsonText) AS DefaultContainer,
       ISJSON(JsonText,VALUE) AS AnyJsonValue,
       ISJSON(JsonText,ARRAY) AS JsonArray,
       ISJSON(JsonText,OBJECT) AS JsonObject,
       ISJSON(JsonText,SCALAR) AS NumberOrString
FROM Inputs
ORDER BY CaseId;

Name the required container

ARRAY accepts the empty array and rejects the empty object. OBJECT makes the opposite choice for those two rows. Empty contents don’t prevent either container from having its stated top-level type.

I’d use the constraint that matches the application’s input contract. A downstream operation expecting object properties shouldn’t accept an array merely because both are valid JSON. The validator’s name should tell a reviewer what kind of input the next step is prepared to process.

Read SCALAR narrowly

SCALAR accepts a JSON number or string. It doesn’t accept the Boolean literal true or the JSON literal null. Both literals remain valid under VALUE, which has the broader top-level value contract.

This distinction is easy to miss if scalar is used loosely in conversation. The output column is named NumberOrString to make its meaning explicit. I’d retain the Boolean and null rows rather than assuming a generic scalar label communicates the function’s precise rule.

Separate malformed text from missing input

The seventh row has malformed object text and returns zero for every check. The last row supplies SQL NULL and returns SQL NULL. A missing string hasn’t been classified as malformed JSON by these results.

I keep those cases separate when designing an error message. A caller who forgot an input needs a different explanation from one who supplied broken JSON. The same displayed blank shouldn’t silently stand for both conditions in a validation report.

State the supported environment

The type-constraint argument was introduced in SQL Server 2022. The presence of the older ISJSON function doesn’t establish support for every argument.

I’d verify the exact target environment before sharing this query as a validation helper. Removing the second argument for compatibility would change its meaning. That decision should be explicit instead of making a narrow shape check silently accept a broader container.

Keep schema checks separate

ISJSON doesn’t verify that object keys are unique at the same level. It also doesn’t validate an application’s required property names, numeric ranges or array element rules. A one result answers the selected JSON type question.

I wouldn’t label that result CompleteValidation. That name would imply more than the function establishes. Add separate checks for the fields that matter. Retain enough input context to explain which rule rejected the document.

Retain every expected row

The example uses eight short typed inputs and performs only SELECT operations. All five validators return int outputs, with NULL preserved for missing text. The output shows all five checks for every input.

I’d compare that combination rather than checking only the number of accepted rows. An incorrect SCALAR interpretation could preserve a total while changing which values pass. Complete small-case results are easier to explain than a broad claim that the JSON checks worked.

Say exactly which shape you expect, and the validator will say it back.

Valid JSON is not a complete schema check, it is one condition in an explicit input contract.

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