使用FOR JSON PATH的子查询返回重复NEWID()值的问题

Let's break down what's happening here, then cover clean solutions that don't require adding extra fields to your subquery.

The Root Cause #

When you wrap a NEWID() subquery with FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, SQL Server's query optimizer treats this as an uncorrelated scalar subquery—meaning it doesn't reference any columns from the outer person table.

Non-deterministic functions like NEWID() usually run once per row in a correlated context, but for uncorrelated scalar subqueries wrapped in FOR JSON, the optimizer often optimizes to execute the subquery only once for the entire result set. That's why every row ends up with the same token value.

When you add person_id to the subquery, it becomes a correlated subquery (it references the outer table's column). This forces the optimizer to re-run the subquery for each row, so NEWID() generates a unique value every time.

Clean Solutions (No Redundant Data) #

You don't need to clutter your subquery with extra fields to get unique tokens. Here are a few straightforward approaches:

1. Use CROSS APPLY Instead of a Subquery #

CROSS APPLY ensures the inner query runs once per row from the outer table, even without referencing external columns:

SELECT TOP 3 
  person_id, 
  j.token
FROM person
CROSS APPLY (
  SELECT NEWID() AS token FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS j

2. Pre-Generate Unique IDs First #

Use a CTE or derived table to generate the NEWID() values per row first, then wrap them in JSON:

WITH PersonWithToken AS (
  SELECT TOP 3 
    person_id, 
    NEWID() AS unique_token
  FROM person
)
SELECT 
  person_id,
  (SELECT unique_token AS token FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS token
FROM PersonWithToken

3. Direct JSON String Concatenation (Simplest) #

If you don't mind building the JSON manually, you can skip the FOR JSON clause entirely and construct the string directly:

SELECT TOP 3
  person_id,
  CONCAT('{"token":"', CAST(NEWID() AS VARCHAR(36)), '"}') AS token
FROM person

All these methods will generate a unique JSON-formatted token for every row, no redundant fields required.

内容的提问来源于stack exchange，提问作者Duane Fields