Converting a JSON column data to tabular format in Azure SQL database

Question

I have a table that has column data as JSON in Azure SQL database, I wanted to change that data into a tabular format (column and rows)

Please find the attached file to refer to the table. [Table data]

Below is the Column data (Column name: Body)of my table.

LOAD #
{
  "loadInfo": {
    "loadNumber": "FT-1",
    "loadId": 700,
    "orderNumber": "FT-1",
    "loadTime": "10-28-2020 00:00:00",
    "bolNumber": "",
    "distance": "136",
    "weight": "0",
    "numberOfPickup": "1",
    "numberOfDrop": "1",
    "status": "Requested"
  },
  "shipperInfo": {
    "shipperName": "American Woodmark Corp-VA",
    "shipperId": null,
    "shipperReferenceNumber": "ERICVA"
  },
  "brokerInfo": {
    "brokerName": " FreightCo, LLC.",
    "brokerId": null,
    "brokerReferenceNumber": null
  },
  "carrierInfo": {
    "carrierName": "FreightCo",
    "carrierId": null,
    "carrierReferenceNumber": "",
    "carrierCode": "634486",
    "codeType": "mc"
  },
  "vehicleInfo": {
    "status": null,
    "eldVendor": "KeepTruckin",
    "vehicleIdentificationNumber": "",
    "licensePlateNumber": "725",
    "eldDriverId": "",
    "driverPhoneNumber": "0000000000"
  },
  "pickupInfo": [
    {
      "address1": "US-23",
      "address2": "",
      "city": "Teutopolis",
      "state": "IL",
      "zipcode": "6267",
      "earliestTime": "10-28-2020 00:01:00",
      "latestTime": "10-28-2020 00:01:00",
      "timezone": "EST",
      "sequence": 1,
      "status": null
    }
  ],
  "dropInfo": [
    {
      "address1": "I-94",
      "address2": "",
      "city": "Glen Carbon",
      "state": "IL",
      "zipcode": "6205",
      "earliestTime": "10-28-2020 01:30:00",
      "latestTime": "10-28-2020 01:30:00",
      "timezone": "EST",
      "sequence": 1,
      "status": null
    }
  ],
  "tenderInfo": {
    "amount": 1787.01,
    "fuelAdvanceAmount": 100,
    "brokerNetAmount": 69.03,
    "fuelAdvanceCommissionAmount": 100,
    "totalBrokerNetAmount": 69.03,
    "tenderTime": "09-13-2020 14:42:00"
  },
  "loadLocationInfo": null
}

I wanted to Display them in the below tabular format and wanted to insert them into the different tables. loadNumber, loadID, orderNumber........... as column name FT1, 700, FT-1 as.......... rows data

Please let me know what functions and methods to use in SQL to convert column JSON data to new table data.

Thank you

https://stackoverflow.com/questions/64167631/how-to-build-a-multi-active-satellite-table-from-a-table-containing-json-query/64167819#64167819 — Amir, Dec 27 '20 at 08:51

score 0 · Answer 1 · answered Dec 28 '20 at 07:16

You can ref the answer which @Amir provided in the comment. It has almost the same question and the answer showed you the example.

You need SQL Server 2016+ to parse the stored JSON using two OPENJSON() calls (with default and explicit schema) and the appropriate APPLY operator.

Azure SQL database also support this function OPENJSON (Transact-SQL).

Ref: How to build a Multi-Active Satellite table from a table containing json query?

Converting a JSON column data to tabular format in Azure SQL database

1 Answers1