更新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);

我想更新Consent元素的值，在值周圍加上引號“”，因為 JSON 是敏感的并會導致錯誤。Consent 元素位于 data.response.GetCustomReportResult.Parameters。同意。

然后我只想將更新后的 JSON 字串列放入此代碼中。可能可以使用 CTE 或子查詢來實作？

    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;

uj5u.com熱心網友回復：

您必須更新列中的值，該值應該是原子的。使用 Json 作為值不是關系。

無論如何，您可以像 Charlieface 所說的那樣在字串中進行替換，因為 json 只是 SQL Server 的 varchar。

uj5u.com熱心網友回復：

您可以使用嵌套的 REPLACE 函式（對于 TRUE 和 FALSE）來更新列，以便參考 VALUE 字串。UPDATE 后 ISJSON 函式回傳 1，這意味著 JSON 有效。

DECLARE @json       table(ApplicationId varchar(20),
                          cijreport     NVARCHAR(MAX));
insert @json(ApplicationId, cijreport) values
('C3272473021100', N'{
"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
 }');

update @json
set cijreport=replace(replace(cijreport, '"Consent":False', '"Consent":"False"'),
                                         '"Consent":True', '"Consent":"True"')
where ApplicationId='C3272473021100';

select isjson(cijreport) is_valid_json from @json;

is_valid_json
1

標籤：sql json sql-server 查询语句 公用表表达式

上一篇：如何從Python中的特定鍵獲取特定值

下一篇：在CarouselBootstrapAjaxJquery中添加活動類