JSON_ARRAY: Choose Whether NULL Elements Are Included

JSON_ARRAY can omit a SQL NULL input or preserve its position as a JSON null element. I choose that policy before comparing array positions. Removing an element changes the relationship between neighboring values.

The default omits missing inputs

The first expression supplies an integer, a missing integer and another integer. Its expected array contains one followed by two. The middle SQL NULL input does not produce an element under the default policy.

JSON_ARRAY uses ABSENT ON NULL by default. That differs from the object constructor’s default property policy. I state the option explicitly when reviewing code that uses both constructors.

The missing input is not converted into an empty string or a numeric zero. It is omitted from the array entirely. That is an element-count change rather than a replacement-value choice.

I return complete array strings in separate columns. Extracting just one nonmissing value would hide the changed array shape. The complete result makes the omission policy visible.

SELECT
    JSON_ARRAY(CAST(1 AS int),CAST(NULL AS int),CAST(2 AS int)) AS DefaultArray,
    JSON_ARRAY(CAST(1 AS int),CAST(NULL AS int),CAST(2 AS int) NULL ON NULL) AS KeptNullArray,
    JSON_ARRAY(CAST(N'1' AS nvarchar(8)),CAST(1 AS int)) AS TypedArray,
    JSON_ARRAY(CAST(N'' AS nvarchar(8)),CAST(NULL AS nvarchar(8)) ABSENT ON NULL) AS EmptyTextArray;

Keep a placeholder when positions have meaning

The second expression requests NULL ON NULL. Its expected array contains one, JSON null and two. The final integer therefore remains in the third position.

A receiving interface might associate array positions with a fixed list of fields. Removing a missing element would shift later values into earlier positions. Retaining the null placeholder can preserve that positional contract.

Another interface might define an array as a collection of available observations. Omitting missing inputs could suit that contract. Neither policy is a universal rule for every payload.

I review the receiver’s meaning before optimizing the number of characters. A shorter array can carry different information. The constructor cannot decide which interpretation the application intended.

Empty strings and typed numbers remain distinct

The third expression supplies a Unicode text one and an integer one. Its expected array quotes the first element and leaves the second as a JSON number. Those elements have different JSON types.

Digits inside a SQL string do not automatically become a JSON number. Converting every source expression to text would change that contract. Keep the intended SQL types visible before construction.

The final expression supplies empty text followed by a missing text value under ABSENT ON NULL. Its expected array contains one quoted empty string. Empty text remains a supplied value while the SQL NULL input is omitted.

An application can normalize empty strings to NULL before construction if its contract requires that transformation. Such normalization belongs in a separate source rule. The NULL option does not itself declare empty strings missing.

Validate the complete array contract

The constructor handles JSON escaping by its own conversion rules. These examples use simple literals to keep element presence central. Real interface tests should include the characters their source allows.

The demonstrated syntax returns nvarchar(max) array text. It does not request a native json storage type. Payload construction and payload storage are separate decisions.

The function is available in SQL Server 2022 and later. This query uses only typed scalar expressions. It changes no tables, transaction settings or database context.

I keep a missing value between two present values in the test model. A missing value only at the end would make the positional change easier to overlook. The middle case exposes exactly where the last value moves.

When adapting the sample, compare both element count and element type against the receiver’s expectations. Keep empty text, missing input and numeric input as separate cases. A visually similar payload can still change an interface contract.

Decide what an empty slot means to the receiver, then pick the NULL option on purpose.

An omitted element is not a null placeholder, it is a shift in every later position.

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