1

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
Ken White
  • 123,280
  • 14
  • 225
  • 444
apolloSN
  • 67
  • 5

0 Answers0