json file to sql table

Question

I have a MDX query and the output in excel looks like below.

but we get a JSON file to automate into sql table (please done ask why cant you se excel instead of json --its big story :-) )
I am unable to insert properly into table. attached JSON file
below is the sql i am using. please help

Declare @json varchar(max)
SELECT @json =BulkColumn
FROM OPENROWSET (BULK 'C:\Desktop\pythontest2\datafile22Copy.JSON', SINGLE_CLOB) import
SELECT *
FROM OPENJSON (@json )
WITH
(
Period_date date
, LiabilityType varchar(250)
, SalesChannel varchar(250)
, Measures varchar(250)
, ReptCustSegCd varchar(250)
, SubscriptionDevices varchar(250)
, totalValues int )

Answer 1

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\temp\datafile22Copy.JSON', SINGLE_CLOB) import


 SELECT --t.value,
Max(Case when d.[Key]=0  then d.value else null end ) 'Period_date',
Max(Case when d.[Key]=1  then d.value else null end ) 'LiabilityType',
Max(Case when d.[Key]=2  then d.value else null end ) 'SalesChannel',
Max(Case when d.[Key]=3  then d.value else null end ) 'Measures',
Max(Case when d.[Key]=4  then d.value else null end ) 'ReptCustSegCd',
Max(Case when d.[Key]=5  then d.value else null end ) 'SubscriptionDevices',
Max(Case when d.[Key]=6  then d.value else null end ) 'totalValues' 
FROM OPENJSON(@JSON, '$.data') t
cross apply openjson(t.value) d
Group by  t.value

Answer 2

SELECT  
/*j.[key],
 j.value, */
 JSON_VALUE(j.value, '$[0]') AS Period_date,
  JSON_VALUE(j.value, '$[1]') AS LiabilityType,
   JSON_VALUE(j.value, '$[2]') AS SalesChannel,
    JSON_VALUE(j.value, '$[3]') AS Measures,
     JSON_VALUE(j.value, '$[4]') AS ReptCustSegCd,
     JSON_VALUE(j.value, '$[5]') AS SubscriptionDevices,
      JSON_VALUE(j.value, '$[6]') AS totalValues

FROM OPENROWSET (BULK 'C:\temp\datafile22Copy.JSON', SINGLE_CLOB) AS r
CROSS APPLY OPENJSON(BulkColumn,'$.data') AS j

Answer 3

Hi，@dilip uddagiri

Welcome to Microsoft T-SQL Q&A Forum!

Please check this：

Declare @JSON varchar(max)  
SELECT @JSON=BulkColumn  
FROM OPENROWSET (BULK 'C:\Desktop\pythontest2\datafile22Copy.JSON', SINGLE_CLOB) as import  
INSERT INTO [yourtable] ([Products], [Line of Business], [Geo subMarket], [Transport Type], [Contract Type], [Entity MR Total Actuals])  
SELECT [LiabilityType], [SalesChannel], [Measures], [ReptCustSegCd ], [SubscriptionDevices], [totalValues ]  
FROM OPENJSON (@JSON)  
WITH  
(  
Period_date date  
, LiabilityType varchar(250) 'strict $.metadata.row[1]'  
, SalesChannel varchar(250)  'strict $.metadata.row[2]'  
, Measures varchar(250)		  'strict $.metadata.row[3]'  
, ReptCustSegCd varchar(250) 'strict $.metadata.row[4]'  
, SubscriptionDevices varchar(250) 'strict $.metadata.row[5]')  
 CROSS APPLY OPENJSON(BulkColumn,'strict $.data') AS j  

Please see here , in the column of totalvalues in the WITH clause , I do not have an explicit output mode specified , because the table index of this column I read is a string ,
and the type you give is int , you can refer to violel's advice to put the Column deletion . Or use cross apply to filter out this part of the values separately and append them together .
For more information on how to convert json data please see this link .

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.