WITHOUT_ARRAY_WRAPPER: Keep JSON Cardinality Explicit

I use WITHOUT_ARRAY_WRAPPER only when the JSON output contract expects one object. Removing brackets doesn’t enforce one source row. Two rows can produce text that isn’t one valid JSON document.

Start with a one-row source

The first query supplies one ItemId and serializes it without the outer array wrapper. Its expected output is one object containing ItemId one. ISJSON reports one for that complete object.

This result fits a single-object consumer, but the source cardinality is part of the contract. I’d keep that requirement visible. The serialization option doesn’t establish that the query can return only one row.

WITH Items AS
(
 SELECT ItemId FROM (VALUES (1)) v(ItemId)
), Generated AS
(
 SELECT (SELECT ItemId FROM Items ORDER BY ItemId
         FOR JSON PATH,WITHOUT_ARRAY_WRAPPER) AS JsonText
)
SELECT JsonText, ISJSON(JsonText) AS IsJsonContainer FROM Generated;

WITH Items AS
(
 SELECT ItemId FROM (VALUES (1),(2)) v(ItemId)
), Generated AS
(
 SELECT (SELECT ItemId FROM Items ORDER BY ItemId
         FOR JSON PATH,WITHOUT_ARRAY_WRAPPER) AS JsonText
)
SELECT JsonText, ISJSON(JsonText) AS IsJsonContainer FROM Generated;

Read the two-row failure as a whole

The second query supplies two ItemId values and uses the same option. Its expected text contains two objects separated by a comma, with no enclosing array. The complete string isn’t valid JSON, so ISJSON returns zero.

Each individual object looks familiar, which can make the output seem acceptable during a quick glance. The consumer receives the complete string, though. I’d validate that whole payload rather than testing a convenient substring or assuming that generated text always forms one document.

Keep array and object contracts distinct

An array is the natural wrapper for multiple row objects. WITHOUT_ARRAY_WRAPPER is useful for a stated single-row result, but it doesn’t change the number of matching rows. Serialization and row selection remain separate operations.

I’d choose the output shape before adding the option. If several rows are legitimate, retain an array contract. If one row is required, verify the selection rule. Also check how the application handles an unexpected additional match.

Do not hide ambiguity with an arbitrary first row

Adding TOP one can make an output object appear valid while discarding additional matching records. That is a separate selection policy, not a generic repair for serialization. A missing ORDER BY can also leave the chosen record unspecified.

I’d first decide whether extra rows are allowed, rejected or selected by a defined ordering rule. The JSON shape should follow that decision. A valid one-object string can still contain the wrong record if the source selection contract is unclear.

Retain named output and validation

The outer SELECT returns JsonText beside its IsJsonContainer result. That keeps the evidence connected to the exact generated payload. The two CTE-based queries use only inline values and perform no writes.

ISJSON is a format check here, not a complete business-schema check. Its one result doesn’t establish required properties or a valid identifier. I retain the full expected text so another reviewer can see what passed or failed the selected container validation.

Review the interface boundary

This option is available in SQL Server 2016 and later. The second example deliberately feeds it two rows to test the contract. It doesn’t require malformed source JSON.

For a real interface, I’d test the allowed row counts and the complete output shape together. Consumers need a predictable object or array contract. Removing a pair of brackets is a small syntax change with a larger meaning when the source cardinality isn’t controlled.

Decide how many rows you expect first, and the rest is easy.

Removing array brackets is not a single-row guarantee, it is a serialization choice that needs a cardinality contract.

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