I am trying to tranform the JSON string into a table format in SQL Server. The elements and values I need are actually in the "GetCustomReportResult" Tag. However I am getting an error message when i try to execute the below query:

Msg 13609, Level 16, State 4, Line 63 JSON text is not properly formatted. Unexpected character 'T' is found at position 916.

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

the JSON String is actually stored in a column in a SQL Table as seen below and I am trying to transform the elements in the tag "GetCustomReportResult" in a table format with columns and values for each of the "ApplicationID".

Here is what I have tried but getting error:

Msg 13609, Level 16, State 2, Line 1 JSON text is not properly formatted. Unexpected character 'o' is found at position 0.

SELECT 
y.cijreport,
y.ApplicationId,
JSON_VALUE(x.value, '$.CIP') as CIP,
JSON_VALUE(x.value, '$.CIQ') as CIQ
--other fields
FROM table as y
CROSS APPLY OPENJSON (cijreport) as x
where cijreport is not null

CodePudding user response：

Two options

If true is a literal, then you are missing quotation.

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

db<>fiddle

If true is a boolean expression, remove the capital T and replace it by t

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

db<>fiddle