Consuming JSON with OPENJSON
-
This question is really a question on the format of the raw JASON as retrieved from the service provider, specifically Google Analytics Reporting.
If I were to use the v3 APIs, the resulting JSON has the typical “key”: “value” pairing throughout the document. However, in the new v4 APIs the resulting JSON is formatted with a cloumnHeader clause that holds the “key” and then the data is returned as data.rows for the “values”. This really makes sense if you think about a very large number of rows.
This is the test case for the v3 version that works:
DECLARE @RawResponse VARCHAR(8000)SET @RawResponse ='{"dimensions": [{
"SessionEventDate": "20170110",
"EventCategory": "CallToOrder",
"EventAction": "Dealer Button Clicked",
"EventLabel":"ShopOecId: 001-76J-2CT,"
}],
}'
SELECT
SessionEventDate
,EventCategory
,EventAction
,EventLabel
FROM OPENJSON(@RawResponse, N'$.dimensions')
WITH (
SessionEventDate VARCHAR(20) N'$.SessionEventDate'
,EventCategory NVARCHAR(250) N'$.EventCategory'
,EventAction NVARCHAR(250) N'$.EventAction'
,EventLabel NVARCHAR(4000) N'$.EventLabel'
) AS dimensions…And returns this result:
SessionEventDate EventCategory EventAction EventLabel 20170110 CallToOrder Dealer Button Clicked ShopOecId: 001-76J-2CT, This the v4 style that I do not yet have working:
DECLARE @RawResponse VARCHAR(8000)SET @RawResponse ='{
"reports": [
{
"columnHeader": {
"dimensions": [
"ga:date",
"ga:eventCategory",
"ga:eventAction",
"ga:eventLabel"
],
"metricHeader": {
"metricHeaderEntries": [
{
"name": "ga:uniqueEventsTemporary",
"type": "INTEGER"
}
]
}
},
"data": {
"rows": [
{
"dimensions": [
"20170112",
"CallToOrder",
"Dealer Button Clicked",
"ShopOecId: 001-066-Y2C,"
],
"metrics": [
{
"values": [
"1"
]
}
]
},
{
"dimensions": [
"20170112",
"CallToOrder",
"Dealer Button Clicked",
"ShopOecId: 001-1N5-IZB,"
],
"metrics": [
{
"values": [
"1"
]
}
]
}
],
"totals": [
{
"values": [
"36548"
]
}
],
"rowCount": 35883,
"minimums": [
{
"values": [
"1"
]
}
],
"maximums": [
{
"values": [
"4"
]
}
],
"isDataGolden": true
},
"nextPageToken": "2"
}
]}'
SELECT
SessionEventDate
,EventCategory
,EventAction
,EventLabel
,UniqueEvent
FROM OPENJSON(@RawResponse, N'$.reports')
WITH (
SessionEventDate VARCHAR(20) N'$.data.rows.dimensions.date'
,EventCategory NVARCHAR(250) N'$.data.rows.dimensions.eventCategory'
,EventAction NVARCHAR(250) N'$.data.rows.dimensions.eventAction'
,EventLabel NVARCHAR(4000) N'$.data.rows.dimensions.eventLabel'
,UniqueEvent INT N'$.data.rows.metrics.uniqueEventsTemporary'
) AS reportResults…And returns this result:
SessionEventDate EventCategory EventAction EventLabel UniqueEvent NULL NULL NULL NULL NULL So what do I have to do to get both of my rows to return?
- michael.french 172 - Thursday, January 19, 2017 9:15 AM
At the first glance, it looks like you are missing the array references in the path.
You an use the likes of http://jsonviewer.stack.hu/ to test / view the path.
- Eirikur Eiriksson - Thursday, January 19, 2017 11:40 AMmichael.french 172 - Thursday, January 19, 2017 9:15 AM
At the first glance, it looks like you are missing the array references in the path.
You an use the likes of http://jsonviewer.stack.hu/ to test / view the path.
One step closer, I can now get one row of data.
SELECT
SessionEventDate
,EventCategory
,EventAction
,EventLabel
,UniqueEvent
FROM OPENJSON(@RawResponse, N'$.reports')
WITH (
SessionEventDate VARCHAR(20) N'$.data.rows[0].dimensions[0]'
,EventCategory NVARCHAR(250) N'$.data.rows[0].dimensions[1]'
,EventAction NVARCHAR(250) N'$.data.rows[0].dimensions[2]'
,EventLabel NVARCHAR(4000) N'$.data.rows[0].dimensions[3]'
,UniqueEvent INT N'$.data.rows[0].metrics[0].values[0]'
) AS reportResultsreturns:
SessionEventDate EventCategory EventAction EventLabel UniqueEvent
20170112 CallToOrder Dealer Button Clicked ShopOecId: 001-066-Y2C, 1So the next step would be to get both rows to return, I have tried changing the root in OPENJSON query but to no avail.
An optional exercise would be to reference the columnHeaders rather than ordinal position in the array.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply