Importing JSON data from a JSON file into SQL Server-CodePudding

I have a JSON file which is something like this:

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

I tried this below code and it gives me all null rows:

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

I also tried CROSS APPLY, but I get nulls only.

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

Any help is greatly appreciated and also I need the solution for Azure Data Warehouse as well I want to implement the solution for both SQL Server and Azure Data Warehouse.

CodePudding user response：

Try an Outer Apply and note the use of AS JSON for Rows. I'm just bringing the first attribute back.

SELECT a FROM OPENJSON(@JSON)
WITH (
    Rows  nvarchar(max) AS JSON) Rows
    OUTER APPLY OPENJSON(Rows.Rows) 
    WITH (
        a nvarchar(254) '$.a') MyObject

CodePudding user response：

You simply need a path ($.Rows in your case) in your OPENJSON() call:

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

Also, note that the sample data doesn't match the d column data type in the explicit schema (the WITH clause).

CodePudding user response：

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