Help with OPENJSON for Google Maps API data

Question

Post reply

Help with OPENJSON for Google Maps API data

tacy.highland

Hall of Fame

Points: 3319
More actions
March 5, 2021 at 10:28 pm
Go to Answer
#3854030
Hello,
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:
```
SELECT *
     FROM OPENJSON(@json, '$.rows.elements')
      WITH (
[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.
Thank you!

Mark Cowne

One Orange Chip

Points: 26952

March 7, 2021 at 9:31 pm

Answer

#3854653

Maybe this?

SELECT o.value AS origin_address,
   d.value AS destination_address,
       JSON_VALUE(e.value,N'$.distance.text') AS [dist_text],
       JSON_VALUE(e.value,N'$.distance.value') AS [dist_value], 
       JSON_VALUE(e.value,N'$.duration.text') AS [dur_text],
       JSON_VALUE(e.value,N'$.duration.value') AS [dur_value]
FROM OPENJSON(@json, '$.rows') r
INNER JOIN OPENJSON(@json, '$.origin_addresses') o ON o.[key] = r.[key]
CROSS APPLY OPENJSON(r.value, '$.elements') e
INNER JOIN OPENJSON(@json, '$.destination_addresses') d ON d.[key] = e.[key];

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Here's a couple of queries that should help

SELECT JSON_VALUE(@json,N'$.destination_addresses[0]') as destination_addresses,
       JSON_VALUE(@json,N'$.origin_addresses[0]') as origin_addresses;


SELECT e.*
FROM OPENJSON(@json, '$.rows') r
CROSS APPLY OPENJSON(r.value, '$.elements') 
WITH ([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' 
) e;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

tacy.highland Hall of Fame Points: 3319 More actions · Answer 2

Excellent, Mark! Thank you!

I tried entering two sets of origin/destinations and it works great too. My next question is how to add the origin/destination values so I know which distance/dur belong to which address pair. The JSON data returned for multiple address pairs is as follows:

Google Maps API JSON

The OPENJSON works great to return all the distance/duration values. The SELECT JSON_VALUE works well to show the first set of address pairs, but how would I add all those dest/origin addresses to the OPENJSON query with the actual dist/duration values so it's all together in one dataset?

I'm guessing the $.rows is what would impact this, right? Since it seems like that makes it skip the origin_addresses and destination_addresses nodes. I'm thinking the challenging part of this is when multiple address pairs are entered, it calculates dist/dur for all combinations of those pairs. So the JSON above returns distance/duration for:

Seattle to Olympia

Seattle to Vancouver

Tacoma to Olympia

Tacoma to Vancouver

Is there a way to pull it all together with the combinations into one dataset so I can see the origin/dest address pairs for each set of dist/duration?

tacy.highland Hall of Fame Points: 3319 More actions · Answer 3

That's it!

Wow, thank you, Mark. This is exactly what I needed. You are awesome.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

You're welcome!

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537