I'm trying to use the TSheets API to pull data into my internal database, but the responses come back in a way that I can't figure out how to efficiently get it into table structure.
I've simplified the response for demo purposes, but it basically looks like this:
{
"results": {
"users": {
"12345": {
"id": 12345,
"first_name": "Demo",
"last_name": "User",
"username": "[email protected]",
"email": "[email protected]"
},
"321123": {
"id": 321123,
"first_name": "John",
"last_name": "Wayne",
"username": "[email protected]",
"email": "[email protected]"
},
"98765": {
"id": 98765,
"first_name": "Suzie",
"last_name": "Q",
"username": "[email protected]",
"email": "[email protected]"
}
}
},
"more": false
}
Instead of an array of users, each user is listed as a separate property with the id as the name of the property. They use this pattern on all the endpoints, so I need a way to know what the structure of the response is going to be in order to query it like I'm used to.
I've written a statement that uses dynamic sql to get this into a table structure, but I was wondering if someone more skilled with the JSON functions could propose a better solution.
Here's my SQL code...
GO
--// simplifed version of the actual json response for security and demo purposes
DECLARE @user_response NVARCHAR(MAX) = N'
{
"results": {
"users": {
"12345": {
"id": 12345,
"first_name": "Demo",
"last_name": "User",
"username": "[email protected]",
"email": "[email protected]"
},
"321123": {
"id": 321123,
"first_name": "John",
"last_name": "Wayne",
"username": "[email protected]",
"email": "[email protected]"
},
"98765": {
"id": 98765,
"first_name": "Suzie",
"last_name": "Q",
"username": "[email protected]",
"email": "[email protected]"
}
}
},
"more": false
}
'
--// put users object into variable
DECLARE @users NVARCHAR(MAX) = (
SELECT users.users
FROM OPENJSON(@user_response)
WITH (results NVARCHAR(MAX) AS JSON
, more VARCHAR(20)) as body
CROSS APPLY OPENJSON(results)
WITH (users NVARCHAR(MAX) AS JSON) as users
)
--// extract the keys from the users object
DECLARE @keys TABLE ([key] VARCHAR(100), [index] INT)
INSERT INTO @keys
SELECT [key], ROW_NUMBER() OVER (ORDER BY [key]) 'index'
FROM OPENJSON(@users)
--// initialize looping variables
DECLARE @i INT = 1
, @key VARCHAR(100)
, @sql NVARCHAR(MAX)
SELECT @sql = 'DECLARE @user_response NVARCHAR(MAX) = N''' + @user_response + ''''
--// loop through keys and UNION individual queries on the original json response
WHILE (@i <= (SELECT MAX([index]) FROM @keys))
BEGIN
SELECT @key = (SELECT [key] FROM @keys WHERE [index] = @i)
SELECT @sql = @sql + CASE WHEN @i = 1 THEN '' ELSE 'UNION' END + '
SELECT user_data.*
FROM OPENJSON(@user_response)
WITH (results NVARCHAR(MAX) AS JSON)
CROSS APPLY OPENJSON(results)
WITH (users NVARCHAR(MAX) AS JSON)
CROSS APPLY OPENJSON(users)
WITH ([' + @key + '] NVARCHAR(MAX) AS JSON)
CROSS APPLY OPENJSON([' + @key + '])
WITH (id INT
, first_name VARCHAR(100)
, last_name VARCHAR(100)
, username VARCHAR(200)
, email VARCHAR(200)) as [user_data]'
SELECT @i = @i + 1
END
--// execute final dynamic query
EXEC sp_executesql @sql
The resultset of this statement looks like this:
|id |first_name|last_name|username |email |
|-----|----------|---------|-----------------|-----------------|
|98765|Suzie |Q |[email protected]|[email protected]|
|321123|John |Wayne |[email protected]|[email protected]|
|12345|Demo |User |[email protected] |[email protected] |
Thanks in advance for your ideas and feedback.
Like this:
select u.*
from openjson(@user_response, '$.results.users') d
cross apply
openjson(d.value)
with
(
id int '$.id',
first_name varchar(200) '$.first_name',
last_name varchar(200) '$.last_name',
username varchar(200) '$.username',
email varchar(200) '$.email'
) u
if you need it you can get the object name with d.[key]