JSON_OBJECT: Decide Whether Missing Values Become Null

JSON_OBJECT decides whether a SQL NULL value becomes a JSON null property or an omitted property. I select that policy from the receiving interface’s contract. An empty string remains a separate present value.

Construct an object from typed expressions

The first expression supplies one present Unicode value and one missing Unicode value. The expected object contains a present property with text AB. It also contains a missing property whose JSON value is null.

That expression leaves the NULL option at its default. JSON_OBJECT uses NULL ON NULL by default. A missing SQL value therefore does not automatically remove its property from this object.

I keep the key names explicit string literals. Each value also has an explicit SQL type. The constructor uses those inputs to serialize an object, rather than treating an arbitrary concatenated string as valid JSON.

The returned value is object text in nvarchar(max) for this syntax. The sample does not request a native json return type. It keeps the constructor contract separate from newer storage-type options.

SELECT
    JSON_OBJECT('present':CAST(N'AB' AS nvarchar(8)),
        'missing':CAST(NULL AS nvarchar(8))) AS DefaultObject,
    JSON_OBJECT('present':CAST(N'AB' AS nvarchar(8)),
        'missing':CAST(NULL AS nvarchar(8)) ABSENT ON NULL) AS OmittedObject,
    JSON_OBJECT('empty':CAST(N'' AS nvarchar(8)),
        'missing':CAST(NULL AS nvarchar(8)) ABSENT ON NULL) AS EmptyStringObject,
    JSON_OBJECT('text':CAST(N'5' AS nvarchar(8)),'number':CAST(5 AS int)) AS TypedObject;

Omitting a property changes the receiving contract

The second expression requests ABSENT ON NULL explicitly. Its expected object contains only the present property. The missing property has been omitted entirely rather than assigned JSON null.

A receiver can distinguish a property that exists with null from a property that is absent. An update interface might attach different meanings to those states. Choose the required meaning before selecting a serializer option.

I wouldn’t remove all missing properties merely to make the payload shorter. The interface may need their names to describe a complete record. The constructor offers a policy choice, not a universal preference.

The example compares both complete strings in separate output columns. It does not infer property absence from how a grid displays one extracted value. That preserves the object-level distinction.

An empty string is still a supplied value

The third expression supplies an empty string and a missing value under ABSENT ON NULL. Its expected object retains the empty property with a quoted empty string. Only the missing property is omitted.

An empty field can therefore remain present in a sparse object. Treating an empty string as missing would require a separate transformation before construction. The NULL option alone does not implement that policy.

I keep this case in an interface test because an all-filled example cannot expose it. Include both empty and missing values when reviewing payload changes. They often arrive through different application paths.

If an application intentionally normalizes empty strings to NULL, document that source transformation. It can change whether a property appears at all. Serialization should not conceal which step changed the input state.

Preserve value types and avoid hand-built JSON

The fourth expression supplies the text five and the integer five. Its expected object quotes the text property and leaves the numeric property as a JSON number. Identical-looking digits do not establish identical JSON types.

A receiving system may validate those types differently. Converting every source value to text before construction changes the payload contract. Keep the intended SQL types visible in the expressions.

The constructor also handles JSON escaping for you. This example uses simple literals so the NULL policy remains central. A real interface should test the characters its source actually permits.

JSON_OBJECT is available in SQL Server 2022 and later. The shown syntax produces object text from typed expressions. Choose the receiver’s property-presence rule before selecting the NULL option.

Keep present text, empty text, missing input and a typed number separate when designing the payload. Compare complete objects against the receiving contract. A formatting-only review can overlook a changed property policy.

Ask the receiver what it expects, and pick the option that says so.

An absent property is not a null property, it is a different statement to the receiver.

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