SQL Server FOR JSON AUTO查询关联States表后StateName变为数组问题

你使用的FOR JSON AUTO模式会自动按照查询中涉及的表的关联关系生成分层JSON结构：当查询同时涉及Address（别名a）和States（别名s）两张表时，AUTO模式会将第二张表States的字段识别为关联子实体，默认生成嵌套结构，所以StateName会被包裹在单独的嵌套层级/数组中。

改用FOR JSON PATH模式即可实现字段平级展示，PATH模式不会自动按表生成分层结构，会直接将你查询的所有字段作为同级属性输出，调整后的查询语句如下：

SELECT e.EmployeeId
        ,(
            SELECT a.Address
                ,a.City
                ,s.StateName AS StateName
            FROM AzureSearch.[Address] AS a
                ,AzureSearch.States AS s
            WHERE a.EmployeeId = e.employeeId
                AND a.City = s.City
            FOR json PATH
            ) AS AddressDetails
FROM azuresearch.Employee AS e
 WHERE e.EmployeeId = 8

调整后返回的AddressDetails结果符合预期，格式如下：

[
    {
     "Address": "4912 La Vuelta",
     "City": "Bothell",
     "StateName": "Washington"
    },
    {
        "Address": "4039 Elkwood Dr.",
        "City": "Ballard",
        "StateName": "Utah"
    }
]

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