如何用T-SQL的FOR JSON语句生成键为字段名的JSON结果？

Problem Statement #

I’ve written this T-SQL query to return JSON results:

SELECT CF.Name , UCF.Value 
FROM dbo.ClaimsCustomFields CF 
LEFT JOIN dbo.UserCustomFields UCF ON UCF.FieldId = CF.Id 
WHERE CF.CustomerId = 2653 
FOR JSON PATH;

Current output:

[ { "Name":"zipCode", "Value":"zip zip zipC zipCod" }, { "Name":"time111zone", "Value":"UTC +2" }, { "Name":"tttt", "Value":"Company organization tessss" } ]

But I want the JSON formatted so each Name value becomes a key paired with its corresponding Value, like this:

[ { "zipCode":"zip zip zipC zipCod" }, { "time111zone":"UTC +2" }, { "tttt":"Company organization tessss" } ]

Is it possible to achieve this with the FOR JSON clause?

Solution #

Absolutely! You can build the desired JSON structure by combining JSON_OBJECT (available in SQL Server 2016+) and STRING_AGG (available in SQL Server 2017+) to dynamically construct each key-value pair object, then wrap them into a valid JSON array.

Here’s the adjusted query:

SELECT CONCAT('[', STRING_AGG(JSON_OBJECT(CF.Name, UCF.Value), ','), ']') AS CustomFieldsJSON
FROM dbo.ClaimsCustomFields CF
LEFT JOIN dbo.UserCustomFields UCF ON UCF.FieldId = CF.Id
WHERE CF.CustomerId = 2653

How it works: #

• JSON_OBJECT(CF.Name, UCF.Value) creates a standalone JSON object for each row, using the Name column value as the key and Value as its paired value (e.g., {"zipCode":"zip zip zipC zipCod"}).
• STRING_AGG(..., ',') concatenates all these individual objects into a comma-separated string.
• CONCAT('[', ..., ']') wraps the concatenated string in square brackets to form a valid JSON array.

Handling NULL Values #

If you need to retain keys even when their corresponding Value is NULL, add the INCLUDE_NULL_VALUES option to JSON_OBJECT:

JSON_OBJECT(CF.Name, UCF.Value, 'INCLUDE_NULL_VALUES')

Alternative for Pre-2017 SQL Server #

If you’re using SQL Server 2016 (which lacks STRING_AGG), use FOR XML PATH to concatenate the objects instead:

SELECT '[' + STUFF((
    SELECT ',' + JSON_OBJECT(CF.Name, UCF.Value)
    FROM dbo.ClaimsCustomFields CF
    LEFT JOIN dbo.UserCustomFields UCF ON UCF.FieldId = CF.Id
    WHERE CF.CustomerId = 2653
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '') + ']' AS CustomFieldsJSON

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