JSON_PATH_EXISTS: Distinguish Missing and Null Properties

I use JSON_PATH_EXISTS when presence matters separately from a property’s value. A property containing JSON null still exists. Asking whether it is present avoids confusing that case with a missing key.

Ask about presence directly

The first document contains a property named a with a JSON null value. Its expected PathPresent result is one. The second document is empty, so the same property path returns zero.

Those documents can produce similar-looking missing scalar results in another extraction workflow. Their structure is different, though. I’d preserve that distinction when checking whether a caller supplied a required key. Its supplied value needs a separate check.

WITH Documents AS
(
 SELECT CaseId, CAST(JsonText AS nvarchar(200)) AS JsonText,
        CAST(PathText AS nvarchar(40)) AS PathText
 FROM (VALUES
 (1,N'{"a":null}',N'$.a'),(2,N'{}',N'$.a'),
 (3,N'{"a":0}',N'$.a'),(4,N'{"a":[]}',N'$.a'),
 (5,N'{"a":[7]}',N'$.a[0]'),(6,N'{"a":[]}',N'$.a[0]'),
 (7,CAST(NULL AS nvarchar(200)),N'$.a')) v(CaseId,JsonText,PathText)
)
SELECT CaseId, JsonText, PathText,
       JSON_PATH_EXISTS(JsonText,PathText) AS PathPresent
FROM Documents
ORDER BY CaseId;

Keep values separate from existence

The third document supplies zero and the fourth supplies an empty array. Both contain the property a, so the property-level path exists. An existence result doesn’t require a nonzero scalar or a nonempty array.

I wouldn’t use PathPresent as a complete validation rule. A required numeric value still needs a type and range check. A required nonempty collection needs an element-level condition, rather than assuming that the collection property guarantees useful contents.

Choose the path at the correct depth

The fifth and sixth rows ask for the first array element. Array indexes begin at zero in these paths. A one-element array supplies that path, while an empty array doesn’t.

The empty array itself still exists at $.a, as the earlier row demonstrates. I’d choose the path according to the business question. Asking for an element and asking for its container can return different answers without either function call being wrong.

Preserve SQL NULL input

The final row supplies SQL NULL rather than a JSON document. Its expected existence result is SQL NULL. That differs from a present property whose JSON value is null, and from an absent property in a supplied document.

I keep the full input and path beside the result. A single integer without those columns hides too much context. A caller can then decide whether missing input should be rejected before applying any property-level rule.

State the version requirement

JSON_PATH_EXISTS is available in SQL Server 2022 and later. That version requirement belongs beside a reusable example, especially when older applications already use other JSON functions. Earlier JSON support doesn’t imply this function is available.

The example uses valid documents and simple paths. Checking one path doesn’t make this function a schema validator. I’d validate the intended document shape separately when the application relies on more than the existence of one path.

Avoid extracting a value just to check a key

The query never requests the property’s scalar contents. It asks the presence question directly. That makes the output easier to explain when null, zero and an empty collection are all meaningful supplied values.

I’d still use another operation when I need the actual value. An existence flag can’t replace a typed extraction result. Keep presence and value checks distinct. Their names should describe what each check establishes.

Keep validation connected to the complete input

All seven rows come from inline VALUES, with explicit text types and a visible path column. There are no application tables or updates. The expected results identify every case instead of reporting only a count of successful matches.

For a real validation rule, I’d add nested objects and the exact property names used by the application. Case and path spelling deserve their own tests. A correct result for $.a doesn’t prove that another path points at the field the caller intended.

Ask the presence question first, and the value questions get easier.

A present path is not a valid business value, it is evidence that the requested location exists.

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