如何用Json_Query等SQL函数提取非空EmployeePictureId并生成JSON数组？

当然可以！我们可以通过组合使用OPENJSON、STRING_AGG和JSON_QUERY这些SQL Server的JSON函数来实现你想要的结果。下面是具体的实现代码和步骤说明：

首先，先还原你的测试数据：

declare @json nvarchar(max) 
set @json = N' { "Companies": [ { "CompanyId": "A", "Employee": null }, { "CompanyId": "B", "Employee": [ { "EmployeePictureId": null, "Name": "Employee1" }, { "EmployeePictureId": "PictureId2", "Name": "Employee2" } ] }, { "CompanyId": "C", "Employee": [ { "EmployeePictureId": null, "Name": "Employee3" }, { "EmployeePictureId": null, "Name": "Employee4" } ] } ] } '

接下来是实现需求的核心代码：

SELECT 
  JSON_QUERY('{"EmployeePictureIds": [' + STRING_AGG('"' + STRING_ESCAPE(EmployeePictureId, 'json') + '"', ',') + ']}') AS Result
FROM 
  OPENJSON(@json, '$.Companies')
  CROSS APPLY OPENJSON(value, '$.Employee')
WHERE 
  EmployeePictureId IS NOT NULL

代码说明： #

• OPENJSON(@json, '$.Companies')：先解析外层的Companies数组，将每个公司的JSON对象拆分成单独的行。
• CROSS APPLY OPENJSON(value, '$.Employee')：针对每个公司的Employee数组进行解析（如果Employee为null，比如公司A，这一步会自动忽略，不会产生行）。
• WHERE EmployeePictureId IS NOT NULL：过滤掉EmployeePictureId为空的记录，只保留非空的ID。
• STRING_AGG('"' + STRING_ESCAPE(EmployeePictureId, 'json') + '"', ',')：把符合条件的EmployeePictureId转换成JSON格式的字符串（带双引号），并用逗号拼接成数组元素的形式。STRING_ESCAPE函数是为了处理ID中可能包含的特殊字符（比如引号、斜杠），确保生成合法的JSON。
• JSON_QUERY(...)：将拼接好的字符串包装成完整的JSON对象，确保返回的结果是合法的JSON类型，而非普通字符串。

执行这段代码后，你会得到想要的结果：

{ "EmployeePictureIds": ["PictureId2"] }

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