如何在SQLServer中查詢Json

我有個問題。我想將一個 JSON 字串填充到一個表中，但是，它只寫了第一行而不是其余的行。我從 rest API 獲取 JSON。

下面是我的代碼和 JSON 檔案的摘錄

DECLARE @token INT;
DECLARE @ret INT;

DECLARE @url nvarchar(max);
DECLARE @Json2 nvarchar(max);
DECLARE @authheader nvarchar(max);
DECLARE @contentType nvarchar(max);

DECLARE @apiKey_lat nvarchar(max);
DECLARE @apiKey_lot nvarchar(max);
DECLARE @apiKey_start nvarchar(max);
DECLARE @apiKey_end nvarchar(max);

DECLARE @apikey nvarchar(max);


DECLARE @json AS TABLE (Json_Table nvarchar(max))
 

SET @authheader = ''
SET @contentType = 'application/json'




SET @url = ''

EXEC @ret = sp_OACreate 'MSXML2.XMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR ('Unable to open HTTP Connection', 10, 1);


EXEC @ret = sp_OAMethod @token, 'open', NULL, 'GET', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'authorization', @authheader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
EXEC @ret = sp_OAMethod @token, 'send'

EXEC sp_OAGetProperty @token, 'responseText', @Json2 OUTPUT;



INSERT INTO @json (Json_Table) EXEC sp_OAGetProperty @token, 'responseText'

SELECT * FROM @json

SELECT * FROM OPENJSON((SELECT * FROM @json))


WITH (
    [parameter] NVARCHAR(MAX) '$.parameter',
    [lat] NVARCHAR(MAX) '$.lat',
    [lon] NVARCHAR(MAX) '$.lon',
    [dates] NVARCHAR(MAX) '$.date',
    [value] NVARCHAR(MAX) '$.value'
) AS MetaData

這是 JSON 片段：

{"data": [
            {
                "parameter": "t_2m:C",
                "coordinates": [
                    {
                        "lat": 51.123456,
                        "lon": -0.123456,
                        "dates": [
                            {
                                "date": "2021-11-17T12:05:00Z",
                                "value": 10.6
                            },
                            {
                                "date": "2021-11-17T13:05:00Z",
                                "value": 11.4
                            }
                        ]
                    }
                ]
            }
        ]
}

我的輸出如下所示：

結果應該是一個表，其中包含來自 JSON 的 LAT、LON、DATE 和 VALUE 值。

這是一個例子：

在此處輸入圖片說明

編輯：我也厭倦了交叉應用。沒有作業。看起來像這樣：

CROSS_APPLY

這是交叉應用的代碼：

DECLARE @token INT;
DECLARE @ret INT;

DECLARE @url nvarchar(max);
DECLARE @Json2 nvarchar(max);
DECLARE @authheader nvarchar(max);
DECLARE @contentType nvarchar(max);

--Set Parameters
DECLARE @apiKey_lat nvarchar(max);
DECLARE @apiKey_lot nvarchar(max);
DECLARE @apiKey_start nvarchar(max);
DECLARE @apiKey_end nvarchar(max);

DECLARE @apikey nvarchar(max);


DECLARE @json AS TABLE (Json_Table nvarchar(max))

--Set Authentications 

SET @authheader = ''
SET @contentType = 'application/json'




SET @url = ''


EXEC @ret = sp_OACreate 'MSXML2.XMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR ('Unable to open HTTP Connection', 10, 1);

EXEC @ret = sp_OAMethod @token, 'open', NULL, 'GET', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'authorization', @authheader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
EXEC @ret = sp_OAMethod @token, 'send'

EXEC sp_OAGetProperty @token, 'responseText', @Json2 OUTPUT;



INSERT INTO @json (Json_Table) EXEC sp_OAGetProperty @token, 'responseText'

SELECT * FROM @json

SELECT * FROM OPENJSON((SELECT * FROM @json))


WITH (
        [data] NVARCHAR(MAX) AS JSON
) AS MetaData



CROSS APPLY OPENJSON([MetaData].[data])
WITH(
    [parameter] NVARCHAR(MAX),
    [coordinates] NVARCHAR(MAX),
    [lat] NVARCHAR(MAX),
    [lon] NVARCHAR(MAX),
    [dates] NVARCHAR(MAX),
    [value] NVARCHAR(MAX)
) AS Test_Metadata

在 SQL 查詢中，缺少其余 API 的 URL 和 Authorization 標頭。

uj5u.com熱心網友回復：

你不應該使用這些sp_OA程式，因為它們充滿了錯誤，難以使用，而且只是為了兼容性。SQL Server 不是通用的腳本語言，請勿將其用作通用腳本語言。

而是使用 Powershell 之類的東西Invoke-WebRequest來下載資料，然后使用Invoke-SqlCmd或將其提供給 SQL Server Invoke-DbaQuery。

如果將其作為引數傳入，則@json可以執行以下操作以獲得所需的結果：

SELECT
  d.parameter,
  coord.lat,
  coord.lon,
  dates.date,
  dates.value
FROM OPENJSON(@json, '$.data[0]')
  WITH (
    parameter nvarchar(100),
    coordinates nvarchar(max) AS JSON
  ) d
CROSS APPLY OPENJSON(d.coordinates)
  WITH (
    lat decimal(9,7),
    lon decimal(9,7),
    dates nvarchar(max) AS JSON
  ) coord
CROSS APPLY OPENJSON(coord.dates)
  WITH (
    date datetimeoffset,
    value decimal(18,2)
  ) dates;

資料庫<>小提琴

注意coordinatesanddates是如何宣告的AS JSON，然后OPENJSON再次傳入using CROSS APPLY。

標籤：sql json sql-server 休息

上一篇：RestfulAPI回應：嵌套物件與添加新欄位

下一篇：我的camundafetchAndLockAPI請求有什么問題。獲取無效請求例外