I'm a newbie when it comes to JSON so when I started down the road of trying to retrieve data back from the Google Maps Distance Matrix API, I knew little to nothing about using APIs. SQL, yes, APIs and JSON, no. So now that I've successfully retrieved a response back from Google Maps, trying to parse it out into a SQL table so I can do something with it has been a challenge. I've read numerous articles to get the hang of it, trying to get this particular JSON data sorted out but nothing is working.
Below is the JSON I'm receiving back and I'm just trying to put it into columns:
The SQL query I'm using is:
FROM OPENJSON(@json, '$.rows.elements')
[dist_text] nvarchar(max) N'$.distance.text',
[dist_value] nvarchar(max) N'$.distance.value',
[dur_text] nvarchar(max) N'$.duration.text',
[dur_value] nvarchar(max) N'$.duration.value'
With @json being the data being received back from the API (above). Not having much experience with this, and not being able to find examples specific to Google Maps Distance Matrix API data, I haven't had much luck. I can see everything with just SELECT * FROM OPENJSON(@json), but it's not in any format for me to do anything with it yet, thus the attempt to make it into columns.
Do I need to include more columns in the WITH statement? Not sure how to handle the "destination_addresses" and "orgin_addresses" part either. Are the nodes I'm calling out in the FROM OPENJSON(@json, '$.rows.elements') wrong here? Are the nodes listed in the columns in the WITH statement wrong?
I'd absolutely appreciate some help from anyone who has any insight.