在SQL中处理JSON数组：统计‘Non Compliant’出现次数的高效方案

嘿，刚接触JSON解析确实容易卡壳，你现在的代码指定了固定的Sections和Fields索引，肯定没法应对数量不固定的表单提交。我给你一个更灵活的方法，能一次性遍历所有Sections下的所有Fields，统计Non Compliant的出现次数：

DECLARE @json NVARCHAR(4000) = N'{ "FormId": "3eb068fe-77c3-4f95-99fc-8313c00ce768", "FormName": "Test Form", "FormVersion": 1.0, "Sections": [ { "Id": "36e612c9-9113-48a7-9415-c9b7200e7376", "Name": "General Details Section", "Title": "General", "Fields": [ { "Id": "a4cedad6-483b-4b42-b42e-12f048b5474e", "Label": "Was the Job Done Safely?", "ValueDataType": "System.String", "Value": "No", "SubFields": [ { "Id": "36593287-bbc4-42cd-914a-eef93a85c6d7", "Label": "What has been done to resolve this?", "ValueDataType": "System.String", "Value": "TEST" }, { "Id": "49164866-3afe-4842-aa6f-85312fd3d558", "Label": "When was this Resolved?", "ValueDataType": "System.DateTime", "Value": "2018-05-04T00:00:00+01:00" } ] } ] }, { "Id": "7b2e4eb9-6f2c-422b-a9fd-c813df293aa5", "Name": "Works Review", "Title": "Works Review", "Fields": [ { "Id": "04a0c54b-7de5-4a14-8ee5-75dade12bfe4", "Label": "Is the Reinstatement correct?", "ValueDataType": "System.String", "Value": "Non Compliant", "SubFields": [ { "Id": "36593287-bbc4-42cd-914a-eef93a85c6d7", "Label": "What has been done to resolve this?", "ValueDataType": "System.String", "Value": "TEST" }, { "Id": "49164866-3afe-4842-aa6f-85312fd3d558", "Label": "When was this Resolved?", "ValueDataType": "System.DateTime", "Value": "2018-05-04T00:00:00+01:00" } ] }, { "Id": "93b4e405-921c-48b3-9dc4-8a1363fb09c9", "Label": "Is the SLG correct on site?", "ValueDataType": "System.String", "Value": "Non Compliant", "SubFields": [ { "Id": "a7847ef3-c413-4a3e-8b6c-2fef4085f77c", "Label": "What has been done to resolve this?", "ValueDataType": "System.String", "Value": "TEST" }, { "Id": "18af548a-3ac5-46e6-ac27-a68232d5670a", "Label": "When was this Resolved?", "ValueDataType": "System.DateTime", "Value": "2018-05-04T00:00:00+01:00" }, { "Id": "9107d373-4207-4e58-9a85-22e466a2c4c7", "Label": "How many Barriers are on site?", "ValueDataType": "System.Decimal", "Value": 4.0 } ] } ] } ] }';

SELECT COUNT(*) AS NonCompliantCount
FROM OPENJSON(@json, '$.Sections') AS sections
CROSS APPLY OPENJSON(sections.value, '$.Fields') AS fields
WHERE JSON_VALUE(fields.value, '$.Value') = 'Non Compliant';

代码解释： #

• OPENJSON(@json, '$.Sections') AS sections：先解析顶层的Sections数组，把每个Section作为一行返回。
• CROSS APPLY OPENJSON(sections.value, '$.Fields') AS fields：对每个Section，再解析它内部的Fields数组，把每个Field作为一行返回——这样不管有多少个Section或Field，都能全部遍历到。
• JSON_VALUE(fields.value, '$.Value') = 'Non Compliant'：提取每个Field的Value属性，筛选出等于Non Compliant的记录，最后用COUNT(*)统计总数。

这个方法完全不需要指定固定的索引，不管表单提交的Sections和Fields数量怎么变，都能准确统计出Non Compliant的次数，比你原来的写法灵活多了。

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