将 JSON 数据从 JSON 文件导入 SQL Server

Question

我有一个像这样的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 Applion ，但我只能获得nulls。

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 Data Data Warehouse的解决方案。

Answer 1

您只需要在 OPENJSON() 调用中添加一个 path（在您的情况下为 $.Rows）：

SELECT *
FROM OPENJSON (@JSON, '$.Rows')
WITH (
   [a] [nvarchar](50) ,
   ...
   [y] [nvarchar](500)
)

另外，请注意，实际的示例数据并不与显式架构（WITH 子句）中的列数据类型不匹配。

Answer 2

尝试外部应用并注意行的 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

Answer 3

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