A property can be present even when its JSON value is null. JSON_PATH_EXISTS checks that presence directly in SQL Server 2022. Pair it with typed ISJSON so a valid document also has the top-level shape your API expects.
Separate Valid JSON From the Right Shape
ISJSON checks whether text conforms to JSON syntax. SQL Server 2022 added a type constraint argument. OBJECT checks a top-level object, while ARRAY checks a top-level array.
A syntactically valid array isn't an acceptable substitute when the API requires an object. Make that distinction at the input boundary rather than discovering it after individual properties have been extracted into application logic.
I start with the payload contract before choosing path expressions. The contract needs a top-level shape, required properties, allowed nulls, and value types. These functions cover different parts of that checklist.
They don't perform a complete schema validation automatically. Keep sample input for each boundary case. A payload can be beautifully formatted and still contain the wrong kind of root for the request.
Compare Object, Array, and Scalar Checks
SCALAR accepts a top-level JSON number or string. VALUE accepts a broader JSON value, including objects, arrays, booleans, and null. Don't use SCALAR as shorthand for every nonobject JSON token.
The differences matter when an endpoint intentionally accepts a single value. Choose the argument matching the documented contract. The familiar word scalar has a specific meaning in this function's syntax.
The sample below supplies each shape directly. Read the returned flags on your own server. These are invented test inputs, not observed API traffic.
The query includes a quoted JSON string and a numeric token so their representation is clear. A SQL NULL input is another case to test separately. It doesn't mean the same thing as text containing the JSON token null.
SELECT v.Payload,
ISJSON(v.Payload,OBJECT) AS IsObject,
ISJSON(v.Payload,ARRAY) AS IsArray,
ISJSON(v.Payload,SCALAR) AS IsScalar,
ISJSON(v.Payload,VALUE) AS IsValue
FROM (VALUES (N'{"id":1}'),(N'[1,2]'),(N'"sample"'),(N'42'),(N'true'),(N'null')) AS v(Payload);Tell Missing From Null With JSON_PATH_EXISTS
JSON_VALUE returns SQL NULL in lax mode for a missing property and for a property containing JSON null. That makes it unsuitable for checking existence on its own. JSON_PATH_EXISTS returns an existence flag for the path.
A present null property still exists. Use that flag when the contract distinguishes a missing field from a deliberate request to clear its value.
I keep both examples in the API validation fixture. The property name and exact path belong in the specification. JSON path matching is case sensitive, so spelling differences deserve a test.
A valid object with CustomerId isn't automatically the same contract as customerId. Consistent naming matters here. The database cannot infer that two differently spelled keys were intended to carry the same meaning.
SELECT v.Payload,
JSON_VALUE(v.Payload,'$.customerId') AS ExtractedId,
JSON_PATH_EXISTS(v.Payload,'$.customerId') AS HasCustomerId
FROM (VALUES (N'{"customerId":null}'),(N'{}'),(N'{"customerId":7}')) AS v(Payload);
Put JSON_PATH_EXISTS in a CHECK Constraint
The sample table stores raw text so the validation functions remain visible. Payload is NOT NULL, avoiding a CHECK that accepts UNKNOWN for a SQL NULL. The constraint requires an object and a present property.
It deliberately accepts a JSON null at that property. A required non-null integer needs a separate extraction and validation rule. Existence alone doesn't establish its type.
The CHECK protects inserts and updates to the stored text. It doesn't validate properties not listed in its expression. Extra keys remain allowed under this sample contract.
Also, ISJSON doesn't enforce unique key names. If duplicate properties are forbidden, detect them through a suitable OPENJSON-based validation step before accepting the payload. Syntax validity and business schema validity have different boundaries.
CREATE TABLE dbo.JsonContractDemo
(
PayloadId int NOT NULL PRIMARY KEY,
Payload nvarchar(max) NOT NULL,
CONSTRAINT CK_JsonContractDemo_Shape CHECK
(ISJSON(Payload,OBJECT) = 1 AND JSON_PATH_EXISTS(Payload,'$.customerId') = 1)
);
INSERT dbo.JsonContractDemo VALUES (1,N'{"customerId":null}'),(2,N'{"customerId":7}');
SELECT PayloadId,Payload FROM dbo.JsonContractDemo;Inspect Types When the Contract Requires Them
OPENJSON returns a type code for each property in an object. That lets you distinguish a numeric token, string token, and explicit null without relying only on a converted value. TRY_CONVERT checks whether a selected value can fit a SQL type, but conversion alone can accept a numeric-looking string. Decide whether that is permitted by the API before calling the validation complete.
In the sample below, customerId comes back as type 2 (number) and note as type 0 (null).
For duplicate keys, group the OPENJSON output by key under an explicit case-sensitive rule matching the payload contract. A standard database collation can group keys more broadly than JSON path matching. Test the chosen rule.
This is a deeper validation step than checking one property's existence. Keep it where the input is accepted, so every downstream reader can rely on the same declared shape.
DECLARE @Payload nvarchar(max) = N'{"customerId":7,"note":null}';
SELECT [key],[value],[type]
FROM OPENJSON(@Payload);Reject Bad Inputs Without Inventing Defaults
A missing required property should produce a clear validation failure. Don't substitute zero unless zero has a defined business meaning. A deliberate JSON null also needs its own policy.
It can mean unknown, clear the field, or invalid input depending on the operation. The existence check makes those distinctions possible. It doesn't choose the policy for an application that hasn't specified one.
Which response should a caller receive for an empty object? Include that in the test set alongside arrays, malformed text, and explicit null values. Keep rejected payloads only under the approved diagnostic policy.
They can contain sensitive input. An error should identify the violated contract without echoing the whole document back into a shared log. Validation needs useful evidence, not an accidental copy of every request.
Confirm the Server Version Before JSON_PATH_EXISTS
These examples require SQL Server 2022 or later for the typed ISJSON arguments and JSON_PATH_EXISTS. Validate the target engine before deploying constraints that depend on them. Test the exact table definition during a rehearsal.
A function available in a development database doesn't automatically exist on an older destination. The version requirement belongs with the deployment prerequisites, not hidden in an error after cutover.
Use JSON_PATH_EXISTS for presence and typed ISJSON for the root shape, then add the value rules the business needs. Keep the fixture small and explicit. A present empty box and a missing box aren't the same delivery.
SQL Server now gives you a direct way to ask which arrived. The contract still decides whether either delivery is acceptable for the request.
Related reading on this blog: Understanding JSON NULL Value Using STRICT Keyword and 2016: Check Value as JSON With ISJSON().
A valid payload is not valid syntax alone, it is syntax and values under an explicit contract.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
