Update value in a JSON String

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: enter image description 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;

2 answers

  • answered 2021-10-24 19:40 Velu

    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 2021-10-24 21:08 SteveC

    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
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum