I have a nested JSON in a sql table column and need to parse out multiple values from the "delinquincies" array. highlighted with ** in the JSON below
{
"creditReportDetails":[
{
"accountStatusSummary":{
"accounts":[
{
"amountOwed":0,
"amountPastDue":0,
"type":"Open-ended"
},
{
"amountOwed":1234,
"amountPastDue":0,
"type":"Revolving"
},
{
"amountOwed":123456,
"amountPastDue":0,
"type":"Installment"
},
{
"amountOwed":123456,
"amountPastDue":0,
"type":"Total"
}
],
**"delinquincies":[
{
"badDebt":null,
"paysAccountasAgreed":34,
"paysPaid120Daysor4PaymentsPastDue":null,
"paysPaid3060DaysorMax2PaymentsPastDue":null,
"paysPaid6090DaysorMax3PaymentsPastDue":null,
"paysPaid90120DaysorMax4PaymentsPastDue":null,
"repossession":null,
"statusNotKnown":null,
"type":"current"
},
{
"badDebt":null,
"paysAccountasAgreed":null,
"paysPaid120Daysor4PaymentsPastDue":null,
"paysPaid3060DaysorMax2PaymentsPastDue":null,
"paysPaid6090DaysorMax3PaymentsPastDue":null,
"paysPaid90120DaysorMax4PaymentsPastDue":null,
"repossession":null,
"statusNotKnown":null,
"type":"history"
}
]**
},
"collections":[
],
"datePulled":"01/01/2020",
"employmentHistory":[
{
"employer":"Some Company",
"historyType":"Current"
},
{
"employer":"Some Company",
"historyType":"Former"
}
],
"personalInfoFromVendor":{
"address":{
"city":"Anywhere",
"line1":"123 Anywhere",
"line2":null,
"postalZipCode":"12345",
"territoryCode":"AB"
},
"dateOfBirth":"01/01/1900",
"firstName":"ABCD",
"lastName":"ABCD",
"middleName":"C",
"ssn":"1231456789",
"suffix":null
},
"reportMessaging":{
"duplicate":"No",
"processingStatus":"Complete",
"referenceNumber":"123456789"
},
"reportSummary":{
"bankruptcies":"No",
"collectionItems":"No",
"expNatlRiskScore":"123",
"publicRecords":"No"
}
}
],
"personalCreditReportUrl":"https://abcdcreditreport.lmig.com/creditreport-details/CRS123456789012",
"subjectId":"CRS123456789012",
"totalCount":1,
"CreditReportBaseResponse":{
"lexisNexisReferenceNumber":"123456789012",
"status":{
"statusCode":"COMPLETE",
"statusDescription":[
"BALANCES ON REVOLVING ACCOUNTS",
"BALANCE-TO-LIMIT RATIOS ON ACCOUNTS"
]
},
"subjectId":"CRS123456789012"
}
}
I've been playing around with various iterations of JSON_Value, feel like I'm missing something simple
Select id,
JSON_VALUE (json_column, '$.creditReportDetails.accountStatusSummary.delinquincies[0].badDebt') as 'Current_Baddebt',
JSON_VALUE (json_column, '$.creditReportDetails.accountStatusSummary.delinquincies[0].paysAccountasAgreed') as 'Current_PaysAccountasAgreed',
JSON_VALUE (json_column, '$.creditReportDetails.accountStatusSummary.delinquincies[0].paysAccountasAgreed') as 'Current_PaysAccountasAgreed',
JSON_VALUE (json_column, '$.creditReportDetails.accountStatusSummary.delinquincies[0].paysPaid3060DaysorMax2PaymentsPastDue') as 'Current_Paid3060DaysorMax2PaymentsPastDue',
JSON_VALUE (json_column, '$.creditReportDetails.accountStatusSummary.delinquincies[0].payspaysPaid6090DaysorMax3PaymentsPastDue') as 'Current_Paid6090DaysorMax3PaymentsPastDue',
JSON_VALUE (json_column, '$.creditReportDetails.accountStatusSummary.delinquincies[0].paysPaid120Daysor4PaymentsPastDue') as 'Current_paysPaid120Daysor4PaymentsPastDue',
JSON_VALUE (json_column, '$.creditReportDetails.accountStatusSummary.delinquincies[0].repossession') as 'Current_repossession',
JSON_VALUE (json_column, '$.creditReportDetails.accountStatusSummary.delinquincies[0].repossession') as 'Current_statusNotKnown',
JSON_VALUE (json_column, '$.creditReportDetails.accountStatusSummary.delinquincies[1].badDebt') as 'Hisorical_Baddebt',
JSON_VALUE (json_column, '$.creditReportDetails.accountStatusSummary.delinquincies[1].paysAccountasAgreed') as 'Hisorical_PaysAccountasAgreed',
JSON_VALUE (json_column, '$.creditReportDetails.accountStatusSummary.delinquincies[1].paysAccountasAgreed') as 'Hisorical_PaysAccountasAgreed',
JSON_VALUE (json_column, '$.creditReportDetails.accountStatusSummary.delinquincies[1].paysPaid3060DaysorMax2PaymentsPastDue') as 'Hisorical_Paid3060DaysorMax2PaymentsPastDue',
JSON_VALUE (json_column, '$.creditReportDetails.accountStatusSummary.delinquincies[1].payspaysPaid6090DaysorMax3PaymentsPastDue') as 'Hisorical_Paid6090DaysorMax3PaymentsPastDue',
JSON_VALUE (json_column, '$.creditReportDetails.accountStatusSummary.delinquincies[1].paysPaid120Daysor4PaymentsPastDue') as 'Hisorical_paysPaid120Daysor4PaymentsPastDue',
JSON_VALUE (json_column, '$.creditReportDetails.accountStatusSummary.delinquincies[1].repossession') as 'Hisorical_repossession',
JSON_VALUE (json_column, '$.creditReportDetails.accountStatusSummary.delinquincies[1].repossession') as 'Hisorical_statusNotKnown'
from Database.dbo.Credit
Expecting Null values and Current_paysAccountasAgreed 34
Here is how to get to the delinquincies.
We need to use CROSS APPLY OPENJSON(...) clause a couple of times due to the nested JSON structure.
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, json_column NVARCHAR(MAX));
INSERT INTO @tbl (json_column)
VALUES
(N'{
"creditReportDetails":[
{
"accountStatusSummary":{
"accounts":[
{
"amountOwed":0,
"amountPastDue":0,
"type":"Open-ended"
},
{
"amountOwed":1234,
"amountPastDue":0,
"type":"Revolving"
},
{
"amountOwed":123456,
"amountPastDue":0,
"type":"Installment"
},
{
"amountOwed":123456,
"amountPastDue":0,
"type":"Total"
}
],
"delinquincies":[
{
"badDebt":null,
"paysAccountasAgreed":34,
"paysPaid120Daysor4PaymentsPastDue":null,
"paysPaid3060DaysorMax2PaymentsPastDue":null,
"paysPaid6090DaysorMax3PaymentsPastDue":null,
"paysPaid90120DaysorMax4PaymentsPastDue":null,
"repossession":null,
"statusNotKnown":null,
"type":"current"
},
{
"badDebt":null,
"paysAccountasAgreed":null,
"paysPaid120Daysor4PaymentsPastDue":null,
"paysPaid3060DaysorMax2PaymentsPastDue":null,
"paysPaid6090DaysorMax3PaymentsPastDue":null,
"paysPaid90120DaysorMax4PaymentsPastDue":null,
"repossession":null,
"statusNotKnown":null,
"type":"history"
}
]
},
"collections":[
],
"datePulled":"01/01/2020",
"employmentHistory":[
{
"employer":"Some Company",
"historyType":"Current"
},
{
"employer":"Some Company",
"historyType":"Former"
}
],
"personalInfoFromVendor":{
"address":{
"city":"Anywhere",
"line1":"123 Anywhere",
"line2":null,
"postalZipCode":"12345",
"territoryCode":"AB"
},
"dateOfBirth":"01/01/1900",
"firstName":"ABCD",
"lastName":"ABCD",
"middleName":"C",
"ssn":"1231456789",
"suffix":null
},
"reportMessaging":{
"duplicate":"No",
"processingStatus":"Complete",
"referenceNumber":"123456789"
},
"reportSummary":{
"bankruptcies":"No",
"collectionItems":"No",
"expNatlRiskScore":"123",
"publicRecords":"No"
}
}
],
"personalCreditReportUrl":"https://abcdcreditreport.lmig.com/creditreport-details/CRS123456789012",
"subjectId":"CRS123456789012",
"totalCount":1,
"CreditReportBaseResponse":{
"lexisNexisReferenceNumber":"123456789012",
"status":{
"statusCode":"COMPLETE",
"statusDescription":[
"BALANCES ON REVOLVING ACCOUNTS",
"BALANCE-TO-LIMIT RATIOS ON ACCOUNTS"
]
},
"subjectId":"CRS123456789012"
}
}');
-- DDL and sample data population, end
SELECT ID
, JSON_VALUE (delinq.[Value], '$.badDebt') as [badDebt]
, JSON_VALUE (delinq.[Value], '$.paysAccountasAgreed') as [paysAccountasAgreed]
-- put here everything else in between
, JSON_VALUE (delinq.[Value], '$.type') as [type]
FROM @tbl AS tbl
CROSS APPLY OPENJSON(tbl. json_column, 'strict $.creditReportDetails') AS cr
CROSS APPLY OPENJSON(cr.value, 'strict $.accountStatusSummary.delinquincies') AS delinq;
Output
+----+---------+---------------------+---------+
| ID | badDebt | paysAccountasAgreed | type |
+----+---------+---------------------+---------+
| 1 | NULL | 34 | current |
| 1 | NULL | NULL | history |
+----+---------+---------------------+---------+