Difficulty with PATH for JSON Data in SQL Server using OPENJSON function

Question

I am now importing JSON data into a SQL Server table where the JSON data is stored as a single column entry.

I now have a more complex JSON structure and I am having difficulty with the path in the OPENJSON statement

I tried creating table results modifying an existing OPENJSON that was pointed out to me.

However this path I can not get the data to appear (the command does complete successfully)

What is incorrect in my path that I am not retrieving the data for **timestamp and value?**

SELECT t.[DATE], j.*
FROM DTReport.Json_synthetic_response_time t
CROSS APPLY OPENJSON(t.log, '$.metrics."builtin:synthetic.browser.visuallyComplete.load".series[2]') WITH (
   [Timestamp] bigint '$[0]',
   [Value] float '$[1]'
) j

Here is the JSON file (with several data points removed for brevity)

 { 
   "totalCount":1,
   "nextPageKey":null,
   "metrics":{ 
      "builtin:synthetic.browser.visuallyComplete.load":{ 
         "series":[ 
            { 
               "dimensions":[ 
                  "SYNTHETIC_TEST-434A9DE59A6CAFD6"
               ],
               "values":[ 
                  { 
                     "timestamp":1571691600000,
                     "value":2978.5833333333335
                  },
                  { 
                     "timestamp":1571702400000,
                     "value":3129.6666666666665
                  },
                  { 
                     "timestamp":1571713200000,
                     "value":3040.6666666666665
                  },
                  { 
                     "timestamp":1571724000000,
                     "value":3132.1666666666665
                  },
                  { 
                     "timestamp":1572901200000,
                     "value":2727.2727272727275
                  }
               ]
            }
         ]
      }
   }
}

score 1 · Accepted Answer · answered Nov 05 '19 at 06:53

You need to parse the series JSON array (which in your case has only one item) and then values JSON array with additional APPLY operator and OPENJSON() call.

JSON:

DECLARE @json nvarchar(max) = N'{ 
   "totalCount":1,
   "nextPageKey":null,
   "metrics":{ 
      "builtin:synthetic.browser.visuallyComplete.load":{ 
         "series":[ 
            { 
               "dimensions":[ 
                  "SYNTHETIC_TEST-434A9DE59A6CAFD6"
               ],
               "values":[ 
                  { 
                     "timestamp":1571691600000,
                     "value":2978.5833333333335
                  },
                  { 
                     "timestamp":1571702400000,
                     "value":3129.6666666666665
                  },
                  { 
                     "timestamp":1571713200000,
                     "value":3040.6666666666665
                  },
                  { 
                     "timestamp":1571724000000,
                     "value":3132.1666666666665
                  },
                  { 
                     "timestamp":1572901200000,
                     "value":2727.2727272727275
                  }
               ]
            }
         ]
      }
   }
}'

Table:

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

Statement:

SELECT d.[DATE], j.*
FROM JSON_TEST d
CROSS APPLY OPENJSON(d.log, '$.metrics."builtin:synthetic.browser.visuallyComplete.load".series[0].values') WITH (
   [Timestamp] bigint '$.timestamp',
   [Value] float '$.value'
) j

Result:

DATE                    Timestamp   Value
05/11/2019 00:00:00 1571691600000   2978.58333333333
05/11/2019 00:00:00 1571702400000   3129.66666666667
05/11/2019 00:00:00 1571713200000   3040.66666666667
05/11/2019 00:00:00 1571724000000   3132.16666666667
05/11/2019 00:00:00 1572901200000   2727.27272727273

Notes:

If the series JSON array has more than one item, you'll need to parse it with additional OPENJSON() call.

I tried adding dimension value by doing another cross apply and selecting "Dimensions" . I get null values . What am i doing wrong? SELECT d.[DATE], j.*,k.* FROM DTReport.JSON_Synthetic_Response_Time d CROSS APPLY OPENJSON(d.log, '$.metrics."builtin:synthetic.browser.visuallyComplete.load".series[0].values') WITH ( [Timestamp] bigint '$.timestamp', [Value] float '$.value' ) j CROSS APPLY OPENJSON(d.log, '$.metrics."builtin:synthetic.browser.visuallyComplete.load".series[0].dimensions') WITH ( [Synth Name] varchar(50) '$.dimensions' ) k — runatyr, Nov 06 '19 at 20:57
Figured it out SELECT d.[DATE], j.*, k.synth FROM DTReport.JSON_TEST d CROSS APPLY OPENJSON(d.log, '$.metrics."builtin:synthetic.browser.visuallyComplete.load".series[0].values') WITH ( [Timestamp] bigint '$.timestamp', [Value] float '$.value' ) j CROSS APPLY OPENJSON(d.log, '$.metrics."builtin:synthetic.browser.visuallyComplete.load".series[0].dimensions') WITH ( [SYNTH] nvarchar(50) '$' ) k — runatyr, Nov 07 '19 at 01:43
@runatyr You are right. `"dimensions"` is an array of scalar values (`"SYNTHETIC_TEST-434A9DE59A6CAFD6"`, probably other values), while `"values"` is an array of `JSON` objects. That's why you need `$` as path in `OPENJSON()` call for `"dimensions"` array. — Zhorov, Nov 07 '19 at 06:38

score 0 · Answer 2 · answered Nov 05 '19 at 03:24

The column_path in your with clause needs to be a valid JSON Path Expression. I think the following gets you what you asked for with a change from series[2] to series[0].

DECLARE @json NVARCHAR(MAX) = '{ 
   "totalCount":1,
   "nextPageKey":null,
   "metrics":{ 
      "builtin:synthetic.browser.visuallyComplete.load":{ 
         "series":[ 
            { 
               "dimensions":[ 
                  "SYNTHETIC_TEST-434A9DE59A6CAFD6"
               ],
               "values":[ 
                  { 
                     "timestamp":1571691600000,
                     "value":2978.5833333333335
                  },
                  { 
                     "timestamp":1571702400000,
                     "value":3129.6666666666665
                  },
                  { 
                     "timestamp":1571713200000,
                     "value":3040.6666666666665
                  },
                  { 
                     "timestamp":1571724000000,
                     "value":3132.1666666666665
                  },
                  { 
                     "timestamp":1572901200000,
                     "value":2727.2727272727275
                  }
               ]
            }
         ]
      }
   }
}'

SELECT * 
FROM OPENJSON(@json, '$.metrics."builtin:synthetic.browser.visuallyComplete.load".series[0].values') 
WITH (
   [Timestamp] bigint '$.timestamp',
   [Value] float '$.value'
) j

Another option you may find useful is to break up the JSON structure into multiple pieces so you don't have to reference a specific index in the series array:

SELECT v.* 
FROM OPENJSON(@json, '$.metrics."builtin:synthetic.browser.visuallyComplete.load"') WITH (Series NVARCHAR(MAX) '$.series' AS JSON) j
CROSS APPLY OPENJSON (j.Series) WITH ([Values] NVARCHAR(MAX) '$.values' AS JSON) s
CROSS APPLY OPENJSON (s.[Values]) WITH ([Timestamp] bigint '$.timestamp', [Value] float '$.value') v

Difficulty with PATH for JSON Data in SQL Server using OPENJSON function

2 Answers2