OPENJSON() optimization for poorly structured API response

Question

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": "demo@gmail.com",
                "email": "demo@gmail.com"
            },
            "321123": {
                "id": 321123,
                "first_name": "John",
                "last_name": "Wayne",
                "username": "notreal@email.com",
                "email": "notreal@email.com"
            },
            "98765": {
                "id": 98765,
                "first_name": "Suzie",
                "last_name": "Q",
                "username": "email@company.com",
                "email": "email@company.com"
            }
        }
    },
    "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": "demo@gmail.com",
                "email": "demo@gmail.com"
            },
            "321123": {
                "id": 321123,
                "first_name": "John",
                "last_name": "Wayne",
                "username": "notreal@email.com",
                "email": "notreal@email.com"
            },
            "98765": {
                "id": 98765,
                "first_name": "Suzie",
                "last_name": "Q",
                "username": "email@company.com",
                "email": "email@company.com"
            }
        }
    },
    "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@company.com|email@company.com|
|321123|John     |Wayne    |notreal@email.com|notreal@email.com|
|12345|Demo      |User     |demo@gmail.com   |demo@gmail.com   |

Thanks in advance for your ideas and feedback.

score 2 · Accepted Answer · answered Jan 15 '22 at 19:29

2

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]

answered Jan 15 '22 at 19:29

David Browne - Microsoft

80,331
6
39
67

2

To add a bit of explanation for the benefit of OP: because the first `OPENJSON` call does not have a schema, it returns every JSON property with the columns `key` `value` and `type`. The first of those is the property name, the second is the entire property value. You then feed that into the next `OPENJSON` using `CROSS APPLY` – Charlieface Jan 15 '22 at 19:46
1

@Charlieface, that's very helpful. I thought I had to know the schema, and doing a "select d.*" in the above solution showed me exactly what you are talking about. Good stuff! I appreciate the quick, solid answers as well as the clarification. Such a great community here. – Peter Jan 15 '22 at 19:55

OPENJSON() optimization for poorly structured API response

1 Answers1