我有一個 JSON 檔案，它是這樣的：
{"Rows":
[{"a":"abc"
,"b":"def"
,"c":"x"
,"d":"yuy"
,"e":"aaa"
,"f":"bcb"
,"g":"wer"
,"h":"www"
,"i":123
,"j":456.0
,"k":"12/1/1999 12:02:49 AM"
,"l":1.000
,"m":52.10
,"n":12.990
,"o":8.40
,"p":3
,"q":8.37
,"r":63.0
,"s":7.2
,"t":"1-dfbaaaf"
,"u":"dppp-9c1"
,"v":"12/28/2066 6:02:48 AM"
,"w":"2824865"
,"x":"123"
,"y":"2-1c-847a-06e27"}
]}
我在下面的代碼中嘗試了這個，它給了我所有的null行：
DECLARE @JSON varchar(max)
SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'C:\f1.json', SINGLE_CLOB) AS j
SELECT *
FROM OPENJSON (@JSON)
WITH (
[a] [nvarchar](50) ,
[b] [nvarchar](50) ,
[c] [nvarchar](50) ,
[d] [int] ,
[e] [nvarchar](50) ,
[f] [nvarchar](50) ,
[g] [nvarchar](50) ,
[h] [nvarchar](50) ,
[i] [nvarchar](50) ,
[j] [nvarchar](50),
[k] [datetime2](7) ,
[l] [nvarchar](50) ,
[m] [nvarchar](50) ,
[n] [nvarchar](50) ,
[o] [nvarchar](50) ,
[p] [nvarchar](50),
[q] [nvarchar](50) ,
[r] [nvarchar](50) ,
[s] [nvarchar](50) ,
[t] [nvarchar](50) ,
[u] [nvarchar](50),
[v] [datetime2](7) ,
[w] [nvarchar](50),
x [nvarchar](50),
y [nvarchar](500)
)
我也試過CROSS APPLY，但我只得到空值。
SELECT *
INTO dbo.Test_JSON
FROM OPENROWSET (BULK 'C\f1.json', Single_CLOB) AS import;
SELECT b, c
FROM dbo.Test_JSON
CROSS APPLY OPENJSON (BUlkColumn)
WITH
(
[Rows] nvarchar(max) AS json,
a uniqueidentifier,
b varchar(50),
c varchar(50)
);
非常感謝任何幫助，我還需要 Azure 資料倉庫的解決方案，我想為 SQL Server 和 Azure 資料倉庫實作解決方案。
uj5u.com熱心網友回復：
您只需要一個path（$.Rows在您的情況下）在您的OPENJSON()通話中：
SELECT *
FROM OPENJSON (@JSON, '$.Rows')
WITH (
[a] [nvarchar](50) ,
...
[y] [nvarchar](500)
)
另請注意，示例資料與顯式架構（子句）中的d列資料型別不匹配。WITH
uj5u.com熱心網友回復：
嘗試外部應用并注意對行使用 AS JSON。我只是把第一個屬性帶回來。
SELECT a FROM OPENJSON(@JSON)
WITH (
Rows nvarchar(max) AS JSON) Rows
OUTER APPLY OPENJSON(Rows.Rows)
WITH (
a nvarchar(254) '$.a') MyObject
uj5u.com熱心網友回復：
Kindly try this..
DECLARE @json NVARCHAR(MAX);
SET @json='{ "a":"abc" ,"b":"def" ,"c":"x" ,"d":"yuy" ,"e":"aaa" ,"f":"bcb" ,"g":"wer" ,"h":"www" ,"i":123 ,"j":456.0 ,"k":"12/1/1999 12:02:49 AM" ,"l":1.000 ,"m":52.10 ,"n":12.990 ,"o":8.40 ,"p":3 ,"q":8.37 ,"r":63.0 ,"s":7.2 ,"t":"1-dfbaaaf" ,"u":"dppp-9c1" ,"v":"12/28/2066 6:02:48 AM" ,"w":"2824865" ,"x":"123" ,"y":"2-1c-847a-06e27"}'
select * from OPENJSON(@json)
------------------------------------------------------------------------
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/456559.html
標籤：sql json sql服务器 天蓝色 天蓝色 sql 数据库
上一篇：SQL計算時間差
下一篇：將選定表用于兩組連接