In a table, I store multiple string records in several records.
declare @x nvarchar(max) = {
"totalSize": 1000,
"done": true,
"records": [
{
"attributes": {
"type": "Contract",
"url": ""
},
"Name": "Harpy",
"Job_Schedule_Date__c": null,
"EndDate": "2021-03-24",
"Account": {
"attributes": {
"type": "Account",
"url": ""
},
"Name": "Madison"
},
"ContractNumber": "12345",
"Related_Site__r": {
"attributes": {
"type": "Site__c",
"url": ""
},
"Name": "Jackson"
}
},
.
.
.
]
}
select * from openJson(@x, '$.records')
I am trying to use open JSON to unpack the records.
I am able to unpack a single record, but it doesn't unpack them into columns and need to unpack multiple records and join them.
Since each record only stores 1000 records, I need to join them up.
What I want is output like below as a Select
Name, Job_Schedule_Date__c, EndDate, AccountName, ContractNumber, RelatedSiteName
Harpy, null, 2021-03-24, Madison, 12345, Jackson