SQL Server JSON列过滤报错：如何修正无效列名问题？

你遇到的问题是因为在JSON_VALUE里引用了pp.PlacementDetails，但这个列是你通过OPENJSON的WITH子句解析出来的嵌套JSON对象，SQL Server无法直接用它来做进一步的JSON路径解析（或者说你这里的引用方式不符合规则）。下面给你两种可行的调整方案：

方案1：直接使用原始表的JSON列进行过滤 #

既然PlacementDetails是原始表Placement的JSON列，我们可以直接对它使用JSON_VALUE，不需要依赖pp别名下的解析列。修改后的SQL如下：

SELECT PlacementId, pp.* 
FROM Placement AS p 
CROSS APPLY OpenJson(p.PlacementDetails) WITH(
    PlacementDetails nvarchar(max) '$.PlacementDetails' AS JSON,
    UserName nvarchar(max) '$.UserName',
    LeadBroker nvarchar(max) '$.LeadBroker',
    PocBroker nvarchar(max) '$.PocBroker',
    AssignedTo nvarchar(max) '$.AssignedTo',
    RenewalDate nvarchar(max) '$.RenewalDate',
    StatusId int '$.StatusId',
    CreatedAt nvarchar(max) '$.CreatedAt',
    SavedBy nvarchar(max) '$.SavedBy',
    ClientName nvarchar(max) '$.ClientName',
    RegionName nvarchar(max) '$.RegionName',
    SavedAt nvarchar(max) '$.SavedAt',
    DueDate nvarchar(max) '$.DueDate',
    Progress nvarchar(max) '$.Progress',
    Premium nvarchar(max) '$.Premium',
    Comments nvarchar(max) '$.Comments',
    Deleted bit '$.Deleted',
    IsLegacyData bit '$.IsLegacyData'
) as pp 
WHERE pp.Deleted = 0
  AND JSON_VALUE(p.PlacementDetails, '$.PlacementDetails[0].Questions[5].Answer') = 'TEMCSER-01'

原因说明 #

这里直接用p.PlacementDetails（原始表的JSON列）作为JSON_VALUE的第一个参数，路径'$.PlacementDetails[0].Questions[5].Answer'是基于原始JSON的根节点$来定位的，这样就绕过了pp里的嵌套JSON列，避免了“无效列名”的错误。

方案2：在OPENJSON中预解析目标Answer字段（更推荐） #

如果你希望把需要过滤的字段也纳入到OPENJSON的解析结果中，可以在WITH子句里新增一个列来提取目标Answer，然后直接在WHERE里过滤这个列。这种方式更清晰，也能减少JSON解析的开销：

SELECT PlacementId, pp.* 
FROM Placement AS p 
CROSS APPLY OpenJson(p.PlacementDetails) WITH(
    PlacementDetails nvarchar(max) '$.PlacementDetails' AS JSON,
    UserName nvarchar(max) '$.UserName',
    LeadBroker nvarchar(max) '$.LeadBroker',
    PocBroker nvarchar(max) '$.PocBroker',
    AssignedTo nvarchar(max) '$.AssignedTo',
    RenewalDate nvarchar(max) '$.RenewalDate',
    StatusId int '$.StatusId',
    CreatedAt nvarchar(max) '$.CreatedAt',
    SavedBy nvarchar(max) '$.SavedBy',
    ClientName nvarchar(max) '$.ClientName',
    RegionName nvarchar(max) '$.RegionName',
    SavedAt nvarchar(max) '$.SavedAt',
    DueDate nvarchar(max) '$.DueDate',
    Progress nvarchar(max) '$.Progress',
    Premium nvarchar(max) '$.Premium',
    Comments nvarchar(max) '$.Comments',
    Deleted bit '$.Deleted',
    IsLegacyData bit '$.IsLegacyData',
    -- 新增列解析目标Answer
    TargetAnswer nvarchar(max) '$.PlacementDetails[0].Questions[5].Answer'
) as pp 
WHERE pp.Deleted = 0
  AND pp.TargetAnswer = 'TEMCSER-01'

原因说明 #

通过在WITH里预解析出TargetAnswer，我们可以像使用普通列一样在WHERE子句中过滤，不仅避免了之前的列名错误，还让SQL语句的可读性更高，同时因为只解析一次JSON，性能也会更好。

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