How do I convert a JSON array into individual values to insert into my SQL table?-CodePudding

Here's a snippet of the json data:

{
  "columns": [
    "State",
    "Honey producing colonies",
    "Yield per colony",
    "Production",
    "Stocks December 15",
    "Average price per pound",
    "Value of production",
    "Year"
  ],
  "rows": [
    [
      "AL",
      "41",
      "20",
      "820",
      "33",
      "0.54",
      "443",
      "1989"
    ],
    [
      "AZ",
      "78",
      "45",
      "3510",
      "1158",
      "0.55",
      "1931",
      "1989"
    ]
]
}

And I've created a table here:

CREATE TABLE HoneyTbl (
     "State" VARCHAR(MAX),
     "Honey producing colonies" VARCHAR(MAX),
     "Yield per colony" VARCHAR(MAX),
     "Production" VARCHAR(MAX),
     "Stocks December 15" VARCHAR(MAX),
     "Average price per pound" VARCHAR(MAX),
     "Value of production" VARCHAR(MAX),
     "Year" VARCHAR(MAX),
)

And now I'm trying to extract the json data of rows here:

Declare @Json varchar(max)
SELECT @Json = BulkColumn
FROM OPENROWSET (BULK 'C:\Temp\honey.json', SINGLE_CLOB) as j
SELECT * FROM OPENJSON (@Json, '$.rows')

But I'm stuck as it's outputting as an array like this:

 --- ------------------------------------------------------------------ 
|key| value                                                            |
 --- ------------------------------------------------------------------ 
| 0 | [   "AL", "41", "20", "820", "33", "0.54", "443", "1989"   ]     |
 --- ------------------------------------------------------------------

How can I select individual values from that array and insert them into my table? I need each one to be under a different column. (Need row values to line up with columns)

CodePudding user response：

Should be able to use OPENJSON's WITH clause to scope in and map each row.

SELECT *  
FROM OPENJSON(@json, '$.rows')  
  WITH (
     [State] VARCHAR(MAX) '$[0]',
     HoneyProducingColonies VARCHAR(MAX) '$[1]',
     YieldPerColony VARCHAR(MAX) '$[2]',
     Production VARCHAR(MAX) '$[3]',
     Stocks VARCHAR(MAX) '$[4]',
     AveragePricePerPund VARCHAR(MAX) '$[5]',
     ValueOfProduction VARCHAR(MAX) '$[6]',
     Year VARCHAR(MAX) '$[7]'
  )

CodePudding user response：

Here is a working sample using JSON_VALUE()

Example or dbFiddle

Select [key]
      ,Pos1 = JSON_VALUE(value,'$[0]')
      ,Pos2 = JSON_VALUE(value,'$[1]')
      ,Pos3 = JSON_VALUE(value,'$[2]')
      ,Pos4 = JSON_VALUE(value,'$[3]')
      ,Pos5 = JSON_VALUE(value,'$[4]')
      ,Pos6 = JSON_VALUE(value,'$[5]')
      ,Pos7 = JSON_VALUE(value,'$[6]')
      ,Pos8 = JSON_VALUE(value,'$[7]')
 From  OpenJson(@json,'$.rows')

Results

key Pos1    Pos2    Pos3    Pos4    Pos5    Pos6    Pos7    Pos8
0   AL      41      20      820     33      0.54    443     1989
1   AZ      78      45      3510    1158    0.55    1931    1989