Importing JSON data from a JSON file into SQL Server

Question

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.

Zhorov · Accepted Answer · 2022-04-07 08:47:40Z

1

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 actual sample data doesn't match the columns data types in the explicit schema (the WITH clause).

edited Apr 7, 2022 at 8:47

answered Apr 6, 2022 at 5:45

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

strattonn · Accepted Answer · 2022-04-06 03:44:44Z

0

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

answered Apr 6, 2022 at 3:44

strattonn

2,0602 gold badges25 silver badges46 bronze badges

Comments

MrinmoyeeG · Accepted Answer · 2022-04-06 06:03:38Z

0

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

answered Apr 6, 2022 at 6:03

MrinmoyeeG

1065 bronze badges

Collectives™ on Stack Overflow

Importing JSON data from a JSON file into SQL Server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related