SQL Server从JSON列提取指定字段的查询问题求助

看起来你的查询没完全覆盖需求，主要问题是没从JSON里提取firstName和lastName，还可能混淆了表字段与JSON字段的关系。我先帮你梳理下正确的写法，再解释问题所在。

假设你的JSON结构 #

首先默认你的shippingAddress列的JSON格式是这样的（如果结构不同可以调整）：

{
  "firstName": "Alice",
  "lastName": "Smith",
  "home": "789 Pine Ave",
  "work": "101 Business Blvd"
}

正确的查询语句 #

SELECT 
    p.id,
    json_extracted.firstName,
    json_extracted.lastName,
    json_extracted.home AS homeAddress,
    json_extracted.work AS workAddress
FROM dbo.person p
CROSS APPLY OPENJSON(p.shippingAddress)
WITH (
    firstName nvarchar(4000) '$.firstName',
    lastName nvarchar(4000) '$.lastName',
    home nvarchar(4000) '$.home',
    work nvarchar(4000) '$.work'
) AS json_extracted

为什么你的原查询不符合预期？ #

1. 未提取JSON里的姓名字段：你原查询的SELECT里直接写了lastName, firstName，如果这两个不是dbo.person表的物理字段（而是JSON内的内容），要么会报错，要么返回错误的值；同时你的OPENJSON WITH子句也没定义这两个字段的映射，自然没法从JSON里取出它们。
2. 路径参数冗余：OPENJSON的第二个参数'$'是默认根路径，完全可以省略，不影响结果。

特殊情况处理：如果home/work是嵌套对象 #

如果你的JSON里home和work是嵌套结构（比如"home": {"street": "XXX", "city": "YYY"}），想要提取嵌套字段的话，可以这么写：

SELECT 
    p.id,
    json_extracted.firstName,
    json_extracted.lastName,
    json_extracted.homeStreet AS homeAddress,
    json_extracted.workCity AS workAddress
FROM dbo.person p
CROSS APPLY OPENJSON(p.shippingAddress)
WITH (
    firstName nvarchar(4000) '$.firstName',
    lastName nvarchar(4000) '$.lastName',
    homeStreet nvarchar(4000) '$.home.street',
    workCity nvarchar(4000) '$.work.city'
) AS json_extracted

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