This is in relation to my previous post located here:

Full JSON String is seen:

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

I want to update value at the Consent element put quotations " " around the value since the JSON is sensitive and causing an error. The element Consent is located at data.response.GetCustomReportResult.Parameters.Consent.

Then I just want to put that updated JSON String column into this code. Probably a CTE or subquery could be used to achieve?

    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;

CodePudding user response：

You have to update the value in the column, which should be atomic. Using Json as a value is not relational.

Anyway, you can make a replace in the string as Charlieface said, because json is just varchar for SQL Server.

CodePudding user response：

You could use nested REPLACE functions (for TRUE and FALSE) to update the column so that the VALUE string is quoted. After UPDATE the ISJSON function returns 1 which means the JSON is valid.

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