CODEWITHSUNDEEP
arraysjsonsql-server
runatyr
runatyr

Reputation: 57

Parse this JSON file to put the values into rows and columns

I have a JSON file that is generated and uploaded nightly into SQL Server as a single entry (This is the code used and an image of the entry)

INSERT INTO DTReport.JSON_TEST (DATE, [log])
Select  getdate(),BulkColumn
FROM OPENROWSET (BULK 'c:\BROWSER.JSON', SINGLE_CLOB) as j

enter image description here

What I want to do is parse the data and create a separate row (and time stamp it) for each entry

So the table would be simply

Col1        Col2                 Col3
(Date)      (Browser Version)   (COUNT)
11/2/2019    IE7                 620
11/2/2019    IE11                599
etc....

Here is the content of the browser.json file. I have tried reading about the OPENJSON function but I am unclear how to loop through the information stored in that single entry and then put it into rows and columns

FILE CONTENTS
BROWSER.JSON
{"extrapolationLevel":1,"columnNames":["top(browserMajorVersion,10)","count(*)"],"values":[["IE 7",620],["IE 11",559],["Chrome 78",477],["Chrome 77",406],["Firefox 69",152],["Edge 18",79],["Chrome Mobile 78",75],["Firefox 70",74],["Safari mobile 13",48],["Chrome 76",43]]}

Upvotes: 2

Views: 1678

Answers (1)

Zhorov
Zhorov

Reputation: 29973

You may use the next example as one possible approach. The values object is a JSON array, so you need an additional APPLY operator using second OPENJSON() call:

JSON data:

DECLARE @json nvarchar(max) = N'{
   "extrapolationLevel":1,
   "columnNames":[
      "top(browserMajorVersion,10)",
      "count(*)"
   ],
   "values":[
      [
         "IE 7",
         620
      ],
      [
         "IE 11",
         559
      ],
      [
         "Chrome 78",
         477
      ],
      [
         "Chrome 77",
         406
      ],
      [
         "Firefox 69",
         152
      ],
      [
         "Edge 18",
         79
      ],
      [
         "Chrome Mobile 78",
         75
      ],
      [
         "Firefox 70",
         74
      ],
      [
         "Safari mobile 13",
         48
      ],
      [
         "Chrome 76",
         43
      ]
   ]
}'

Table:

CREATE TABLE JSON_TEST (DATE date, [log] nvarchar(max))
INSERT INTO JSON_TEST (DATE, [log]) 
VALUES (GETDATE(), @json)

Statement:

SELECT t.[DATE], j.*
FROM JSON_TEST t
CROSS APPLY OPENJSON(t.log, '$.values') WITH (
   BrowserVersion varchar(50) '$[0]',
   [Count] int '$[1]'
) j

Result:

DATE        BrowserVersion  Count
03/11/2019  IE 7              620
03/11/2019  IE 11             559
03/11/2019  Chrome 78         477
03/11/2019  Chrome 77         406
03/11/2019  Firefox 69        152
03/11/2019  Edge 18            79
03/11/2019  Chrome Mobile 78   75
03/11/2019  Firefox 70         74
03/11/2019  Safari mobile 13   48
03/11/2019  Chrome 76          43

Notes (JSON basics):

  • When you want to parse JSON string and get results as table, use OPENJSON table-valued function, with default or explicit schema.
  • Function JSON_QUERY extracts an object or an array from a JSON string. If the value is not an object or an array, the result is NULL in lax mode and an error in strict mode.
  • Function JSON_VALUE extracts a scalar value from a JSON string. If the path points to not a scalar value, the result is NULL in lax mode and an error in strict mode

Upvotes: 1

Related Questions