Retrieve_JSON_File_Values.sql

 /*

 Declare @JSON varchar(max)
 SELECT @JSON=BulkColumn
 FROM OPENROWSET (BULK 'C:\temp\datafile22Copy.JSON', SINGLE_CLOB) import
 --datafile22Copy.JSON content is the same in the following string

 */
declare @json varchar(max)='{ "metadata" : {"page" : ["LineOfBusiness","GeoSubMarket","TransportType","ContractType","Entity_MR","SourceType","Products","MRCTiers","DesignatedMarketArea"],"column" : ["ScenarioSource"],"row" : ["Period","LiabilityType","SalesChannel","Measures","ReptCustSegCd","SubscriptionDevices"]}
,"data" : [["","","","","","","TOTAL_ACT"],["01-25-22","TOTSIGCON","BRANDED","GROSSUPG","S604","VOICEDEV","1.0"],["01-25-22","TOTSIGCON","BRANDED","GROSSUPG","X013","VOICEDEV","22400.0"],["01-25-22","TOTSIGCON","BRANDED","GROSSUPG","X013","NONVOICEDEV","708.0"],["01-25-22","TOTSIGCON","BRANDED","GROSSUPG","BUABSBMM","VOICEDEV","1.0"]] }'

 

 --Option1
 select *
 from openjson(@json, '$.data') 
 with
 (
     Period_date         date         '$[0]',
     LiabilityType       varchar(250) '$[1]',
     SalesChannel        varchar(250) '$[2]',
     Measures            varchar(250) '$[3]',
     ReptCustSegCd       varchar(250) '$[4]',
     SubscriptionDevices varchar(250) '$[5]',
     totalValues         varchar(10)  '$[6]'
 )
 order by Period_date




  --Option2

  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

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