loading...

JSON Get Request with Transact SQL

codingsafari profile image Nico Braun ・3 min read

Modern SQL Server has many nice features and build in functions. Today I will use the build in procedure xp_cmdshell in combination with Powershell to implement my own get request procedure. Afterwards I am going to use the OPENJSON function to parse the response of some JSON API Requests.

Implementing a Web Get Request

Let's implement a reusable procedure to make HTTP get requests.

CREATE PROCEDURE wget @url VARCHAR(255), @payload NVARCHAR(max) OUTPUT
as begin
declare @op table (op nvarchar(max))
declare @c varchar(1000) = N'powershell.exe -noprofile -executionpolicy bypass'
    + N'-command (Invoke-WebRequest -Uri "'+@url+'" -UseBasicParsing).content'
insert into @op exec xp_cmdshell @c
SELECT @payload = STRING_AGG(op, '') FROM @op
end
GO

@op is used to store output temporarily, as the result from xp_cmdshell will be split up into multiple rows, based on the output string length.
Since we are interested in keeping the output intact, we are going to use STRING_AGG to select the concatenated rows into our OUTPUT variable @payload

Enable xp_cmdshell

It is likely that xp_cmdshell is not enabled on your server. You can enable it with the following script.

-- To allow advanced options to be changed.  
EXEC sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO

OPENJSON

Our new wget procedure is not strictly bound to JSON and will return any response content as one long string. However, for this post I will make requests to a JSON API.

declare @jsn nvarchar(max)
EXEC wget @url = 'https://jsonplaceholder.typicode.com/posts/1',
    @payload = @jsn output

The default use of OPENJSON selects the keys vertically into a key column.

SELECT * FROM OPENJSON (@jsn, N'$')
Key Value
userId 1
id 1
title sunt aut facere repellat provident occaecati excepturi optio reprehenderit
body quia et suscipit suscipit recusandae consequuntur expedita et cum reprehenderit molestiae ut ut quas totam nostrum rerum est autem sunt rem eveniet architecto

We can use a with clause to to more sophisticated selects. For example, could select each key into a column with the same name.

SELECT *
FROM OPENJSON (@jsn, N'$')
  WITH (
    UserId int N'$.userId',
    ID int N'$.id',
    Title varchar(255) N'$.title',
    Body varchar(1000) N'$.body'
  )
UserId ID Title Body
1 1 sunt aut facere repellat provident occaecati excepturi optio reprehenderit quia et suscipit suscipit recusandae consequuntur expedita et cum reprehenderit molestiae ut ut quas totam nostrum rerum est autem sunt rem eveniet architecto

Arrays and Nested Objects

Sometimes the response is a nested object, an array or even an array of nested objects.

declare @jsn nvarchar(max)
EXEC wget @url = 'http://dummy.restapiexample.com/api/v1/employees',  @payload = @jsn output

SELECT *
FROM OPENJSON (@jsn, N'$')
WITH (
    [Status] nvarchar(55)  N'$.status',
    [data]  nvarchar(MAX)  AS JSON
)
Status data
success [{"id":"1","employee_name":"Ti...

OPENJSON is quite flexible, and we can get our employees.

SELECT *
FROM OPENJSON (@jsn, N'$.data')
  WITH (
    ID int N'$.id',
    [Name] nvarchar(255) N'$.employee_name',
    Salary int N'$.employee_salary',
    Age int  N'$.employee_age'
  )
ID Name Salary Age
1 Tiger Nixon 320800 61
2 Garrett Winters 170750 63
3 Ashton Cox 86000 66
4 Cedric Kelly 433060 22
5 Airi Satou 162700 33

Documentation

I would also recommend reading the official OPENJSON documentation.

Discussion

pic
Editor guide