SQL Server修正JSON指定节点值并结合OPENJSON完成查询的方法

问题描述 #

我有一张SQL表，其中某列存储了JSON字符串，如下图所示：

完整JSON字符串示例代码如下：

DECLARE @json NVARCHAR(MAX)
SET @json=
'{
"status":"ok",
"data":{
  "response":{
     "GetCustomReportResult":{
        "CIP":null,
        "CIQ":null,
        "Company":null,
        "ContractOverview":null,
        "ContractSummary":null,
        "Contracts":null,
        "CurrentRelations":null,
        "Dashboard":null,
        "Disputes":null,
        "DrivingLicense":null,
        "Individual":null,
        "Inquiries":{
           "InquiryList":null,
           "Summary":{
              "NumberOfInquiriesLast12Months":0,
              "NumberOfInquiriesLast1Month":0,
              "NumberOfInquiriesLast24Months":0,
              "NumberOfInquiriesLast3Months":0,
              "NumberOfInquiriesLast6Months":0
           }
        },
        "Managers":null,
        "Parameters":{
           "Consent":True,
           "IDNumber":"124",
           "IDNumberType":"TaxNumber",
           "InquiryReason":"reditTerms",
           "InquiryReasonText":null,
           "ReportDate":"2021-10-04 06:27:51",
           "Sections":{
              "string":[
                 "infoReport"
              ]
           },
           "SubjectType":"Individual"
        },
        "PaymentIncidentList":null,
        "PolicyRulesCheck":null,
        "ReportInfo":{
           "Created":"2021-10-04 06:27:51",
           "ReferenceNumber":"60600749",
           "ReportStatus":"SubjectNotFound",
           "RequestedBy":"Jir",
           "Subscriber":"Credit",
           "Version":544
        },
        "Shareholders":null,
        "SubjectInfoHistory":null,
        "TaxRegistration":null,
        "Utilities":null
     }
  }
 },
 "errormsg":null
 }'
 SELECT * FROM OPENJSON(@json);

当前JSON中位于路径data.response.GetCustomReportResult.Parameters.Consent的节点值True未包裹双引号，不符合JSON格式要求导致解析报错，需要给该值添加双引号修正JSON格式。
请问如何通过CTE或者子查询等方式，使用修正后的JSON列执行如下查询逻辑？

SELECT 
y.cijreport,
y.ApplicationId,
x.CIP,
x.CIQ
--other fields
FROM myTable as y
CROSS APPLY OPENJSON (updated_cijreport, '$.data.response')
WITH (
CIP nvarchar(max) AS JSON,
CIQ nvarchar(max) AS JSON
) AS x;

解决方案 #

先通过字符串替换修正JSON中的非法格式，再用CTE封装修正后的数据执行查询即可，实现代码如下：

WITH corrected_table AS (
    SELECT
        cijreport,
        ApplicationId,
        -- 替换未加引号的True为符合JSON规范的字符串
        REPLACE(updated_cijreport, '"Consent":True', '"Consent":"True"') AS fixed_cijreport
    FROM myTable
)
SELECT 
    y.cijreport,
    y.ApplicationId,
    x.CIP,
    x.CIQ
    -- 其他需要的字段
FROM corrected_table as y
CROSS APPLY OPENJSON (y.fixed_cijreport, '$.data.response')
WITH (
    CIP nvarchar(max) AS JSON,
    CIQ nvarchar(max) AS JSON
) AS x;

如果数据中存在true、TRUE等大小写不同的写法，可以叠加多层REPLACE处理，覆盖所有变体：

REPLACE(REPLACE(REPLACE(updated_cijreport, '"Consent":True', '"Consent":"True"'), '"Consent":true', '"Consent":"True"'), '"Consent":TRUE', '"Consent":"True"')

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