Use T-SQL OPENJSON for accessing json data

  • 26 March 2020
  • 2 replies
  • 200 views

Userlevel 2
Badge +2

Hello,

From SQL Server 2016 and later you have extra functionality to process data in JSON format, for example using OPENJSON. Below you will find an example of reterieving Geo data from the LocationIQ API. You will need your own API Key, but it is available for free.

NOTE

The OPENJSON function is available only under compatibility level 130 or higher. If your database compatibility level is lower than 130, SQL Server can't find and run the OPENJSON function. Other JSON functions are available at all compatibility levels.

You can check compatibility level in the sys.databases view or in database properties. You can change the compatibility level of a database with the following command:

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

Compatibility level 120 may be the default even in a new Azure SQL Database.

T-SQL Example:

declare @response table (txt nvarchar(max));
declare @object int
, @street nvarchar(100)
, @street_number nvarchar(10)
, @postcode nvarchar(20)
, @city nvarchar(50)
, @country nvarchar(100)
, @url_endpoint nvarchar(100)
, @api_key nvarchar(100)
, @response_format nvarchar(5)
, @request nvarchar(250)
, @addressdetails nvarchar(1)


set @api_key = 'can you get for free at https://locationiq.com/'
set @street = 'Ovenbouwershoek'
set @street_number = '9'
set @postcode = '7328JH'
set @city = 'Apeldoorn'
set @country = 'Netherlands'
set @url_endpoint = 'https://eu1.locationiq.com/v1/search.php?key='
set @response_format = 'json'
set @addressdetails = '1'

set @request = @url_endpoint +
@api_key + '&format=' + @response_format + '&addressdetails=' + @addressdetails +
'&street=' + coalesce(@street,'') + ' ' + coalesce(@street_number,'') +
'&postalcode=' + coalesce(@postcode,'') +
'&city='+ coalesce(@city,'') +
'&country='+ coalesce(@country,'')

exec sp_oacreate 'msxml2.xmlhttp', @object out;
exec sp_oamethod @object, 'open', null, 'get',
@request,
'false';
exec sp_oamethod @object, 'send';

insert into @response (txt)
exec sp_oamethod @object, 'responsetext'

exec sp_oadestroy @object

declare @json nvarchar(max)

select @json = txt from @response

select *
from openjson(@json)
with (
place_id nvarchar(200) '$.place_id'
, osm_type nvarchar(50) '$.osm_type'
, osm_id bigint '$.osm_id'
, lat numeric(11,7) '$.lat'
, lon numeric(11,7) '$.lon'
, display_name nvarchar(max) '$.display_name'
, class nvarchar(100) '$.class'
, type nvarchar(100) '$.type'
, importance numeric(4,3) '$.importance'
, street_number nvarchar(10) '$.address.house_number'
, street nvarchar(100) '$.address.road'
, suburb nvarchar(100) '$.address.suburb'
, city nvarchar(50) '$.address.city'
, state nvarchar(50) '$.address.state'
, postcode nvarchar(20) '$.address.postcode'
, country nvarchar(100) '$.address.country'
, country_code nvarchar(3) '$.address.country_code'
, city_district nvarchar(100) '$.address.city_district'
, town nvarchar(100) '$.address.town'
)
;

 


2 replies

Badge +1

Hello @coolrunnin ,

 

Do you use this in combination with the google maps extender?

 

Best, Guy

Userlevel 2
Badge +2

Hello Guy,

That will be the idea in the near future, but not only the Maps extender. I work for a Logistics company, so valid address data is important. We receice a lot of EDI messages which have to be validated.

Next to that, you can use this example to send requests to other Rest API’s.

Regards,

Randolph

 

Reply