FOR JSON PATH: Choose Whether NULL Properties Appear

I choose FOR JSON PATH null handling as part of the output contract. By default, a SQL NULL column doesn’t produce a property. INCLUDE_NULL_VALUES makes that property appear with a JSON null value.

Keep the same source rows

Both statements serialize the same three supplied rows. One has a label, one has SQL NULL, and one has an empty string. ItemId remains present in every object, so the changed property behavior is easy to locate.

I don’t change the source values to demonstrate the option. The output shape changes because the serialization rule changes. That distinction makes the proposed change easier to locate. It belongs either in the source query or in the serialization contract.

WITH Items AS
(
 SELECT ItemId, CAST(Label AS nvarchar(20)) AS Label
 FROM (VALUES (1,N'blue'),(2,CAST(NULL AS nvarchar(20))),
              (3,N'')) v(ItemId,Label)
)
SELECT (SELECT ItemId,Label FROM Items ORDER BY ItemId
        FOR JSON PATH) AS JsonText;

WITH Items AS
(
 SELECT ItemId, CAST(Label AS nvarchar(20)) AS Label
 FROM (VALUES (1,N'blue'),(2,CAST(NULL AS nvarchar(20))),
              (3,N'')) v(ItemId,Label)
)
SELECT (SELECT ItemId,Label FROM Items ORDER BY ItemId
        FOR JSON PATH,INCLUDE_NULL_VALUES) AS JsonText;

Read the omitted property

The default output’s second object contains ItemId but no Label property. The SQL NULL hasn’t become an empty string or the word null. Its property is absent from that generated object.

I’d check how the consumer interprets absence before accepting this default. Some interfaces distinguish an omitted field from an explicitly supplied null. A serializer that produces valid JSON can still communicate the wrong update or display rule to its caller.

Include the JSON null explicitly

The second statement adds INCLUDE_NULL_VALUES. Its second object now contains Label with the JSON value null. The first and third objects retain their original values, including the empty string.

This option doesn’t fill a missing label with useful text. It preserves the property’s presence while representing its missing value explicitly. I’d keep that distinction in the API contract. This option doesn’t repair incomplete data.

Keep empty text separate

The third object’s Label property appears in both outputs with an empty string. That source value isn’t SQL NULL, so the option doesn’t change it. Empty and missing remain different cases in the generated JSON.

I’d retain this row during validation because a quick visual review can treat both as blank. The complete output text makes the difference inspectable. A downstream display can choose a friendly label, but that shouldn’t silently change the serialized value.

Make the generated sequence explicit

ORDER BY ItemId appears inside each JSON-producing subquery. It states the intended array sequence for these three objects. The outer SELECT gives the complete generated text a stable column name.

I’d compare the whole output, including property names and values, rather than checking only that it parses as JSON. Validity doesn’t prove the intended null policy or row order. A consumer that depends on those details needs them tested directly.

Keep serialization within its role

FOR JSON support starts with SQL Server 2016. The example uses only inline values and SELECT operations, with no application writes. Its two complete expected strings expose one serialization choice without introducing unrelated setup.

For a real interface, I’d document whether missing and null mean different actions. Choose the option that expresses that rule, then review nested objects separately. The fact that a property appears doesn’t establish that its value is acceptable or that a later write should occur.

Decide what absence means first, then pick the option.

An omitted property is not JSON null, it is a different output contract that the consumer must understand.

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