針對結構不佳的API回應的OPENJSON()優化

我正在嘗試使用 TSheets API 將資料提取到我的內部資料庫中，但是回應以一種我無法弄清楚如何有效地將其放入表結構的方式回傳。

出于演示目的，我已經簡化了回應，但它基本上看起來像這樣：

{
    "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
}

每個用戶都被列為一個單獨的屬性，而不是一組用戶，而 id 作為屬性的名稱。他們在所有端點上都使用這種模式，所以我需要一種方法來了解回應的結構將是什么，以便像我習慣的那樣查詢它。

我已經撰寫了一個使用動態 sql 將其放入表結構的陳述句，但我想知道是否有更熟練的 JSON 函式的人可以提出更好的解決方案。

這是我的 SQL 代碼...

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

此陳述句的結果集如下所示：

|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   |

提前感謝您的想法和反饋。

uj5u.com熱心網友回復：

像這樣：

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
 

如果你需要它，你可以得到物件名稱 d.[key]

標籤：

上一篇：針對div上的簡單邊框案例優化代碼(HTML/CSS)

下一篇：使用string_split將資料存盤在多列中，而不僅僅是一列？