Update value in a JSON String

Question

This is in relation to my previous post located here: Link to Previous Post

There is this JSON String in a column in a SQL Table:

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;

Velu · Accepted Answer · 2021-10-24 19:40:20Z

1

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.

answered Oct 24, 2021 at 19:40

Velu

564 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SteveC · Accepted Answer · 2021-10-24 21:08:54Z

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

Collectives™ on Stack Overflow

Update value in a JSON String

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related