Compatibility must be 130 or above to run openjson command
http://jsonviewer.stack.hu/
https://stackoverflow.com/questions/37218254/sql-server-openjson-read-nested-json?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa
declare @json nvarchar(max)
set @json = '
[
{
"IdProject":"97A76363-095D-4FAB-940E-9ED2722DBC47",
"Name":"Test Project",
"structures":[
{
"IdStructure":"CB0466F9-662F-412B-956A-7D164B5D358F",
"IdProject":"97A76363-095D-4FAB-940E-9ED2722DBC47",
"Name":"Test Structure",
"BaseStructure":"Base Structure",
"DatabaseSchema":"dbo",
"properties":[
{
"IdProperty":"618DC40B-4D04-4BF8-B1E6-12E13DDE86F4",
"IdStructure":"CB0466F9-662F-412B-956A-7D164B5D358F",
"Name":"Test Property 2",
"DataType":1,
"Precision":0,
"Scale":0,
"IsNullable":false,
"ObjectName":"Test Object",
"DefaultType":1,
"DefaultValue":""
},
{
"IdProperty":"FFF433EC-0BB5-41CD-8A71-B5F09B97C5FC",
"IdStructure":"CB0466F9-662F-412B-956A-7D164B5D358F",
"Name":"Test Property 1",
"DataType":1,
"Precision":0,
"Scale":0,
"IsNullable":false,
"ObjectName":"Test Object",
"DefaultType":1,
"DefaultValue":""
}
]
}
]
}
]';
select
Projects.IdProject, Projects.Name as NameProject,
Structures.IdStructure, Structures.Name as NameStructure, Structures.BaseStructure, Structures.DatabaseSchema,
Properties.*
from openjson (@json)
with
(
IdProject uniqueidentifier,
Name nvarchar(100),
structures nvarchar(max) as json
)
as Projects
cross apply openjson (Projects.structures)
with
(
IdStructure uniqueidentifier,
Name nvarchar(100),
BaseStructure nvarchar(100),
DatabaseSchema sysname,
properties nvarchar(max) as json
) as Structures
cross apply openjson (Structures.properties)
with
(
IdProperty uniqueidentifier,
NamePreoperty nvarchar(100) '$.Name',
DataType int,
[Precision] int,
[Scale] int,
IsNullable bit,
ObjectName nvarchar(100),
DefaultType int,
DefaultValue nvarchar(100)
)
as Properties
************************************
USE [JS]
GO
/****** Object: StoredProcedure [dbo].[DashboardJSON] Script Date: 5/10/2018 6:07:27 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Naveen Gupta
-- Create date: 07-May-2018
-- Description: To Find out the relevant values for Localization table from Dashboard JSON
-- =============================================
--EXEC DashboardJSON '{"Employees":[{"uid":"ctrl_2yvnm1g77","data":"{\"kpioverview\":{\"title\":\"KPI Overview\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77\",\"height\":\"\",\"show_gap_column\":false,\"show_traffic_light\":false,\"show_Value\":false,\"Define_Orange\":\"\",\"show_benchmark_analysis\":false,\"benchmark_analysis_columns\":\"1\",\"show_trend_graph\":false,\"show_map\":false,\"filter_top1_percent\":\"\",\"filter_top2_percent\":\"\",\"filter_bottom1_percent\":\"\",\"filter_bottom2_percent\":\"\",\"kpicolumns\":{\"col1\":\"Last\",\"col2\":\"DOECountryAvg\",\"col3\":\"DOERegionAvg\",\"col4\":\"DOEVolumeGroupAvg\"},\"kpimap\":{\"hierarchy\":\"\",\"allowedallcheck\":\"true\",\"filter\":\"alldealer\"},\"kpi\":[{\"kpitext\":\"Total CNHI Parts Inventory - em % Ativo Total\",\"id\":\"assets.total.cnhi.inventories.parts.perc.value_P\",\"order\":\"0\",\"reverse_logic\":false,\"red_value\":\"100.00\",\"orange_value\":\"100.00 to 500.00\",\"green_value\":\"500.00\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77_assets.total.cnhi.inventories.parts.perc.value_P\"}]}}"},{"uid":"ctrl_1gyawpvos","data":"{\"dealerprofile\":{\"title\":\"Dealer Profile Overview\",\"resourceId\":\"DashNAFTA_42_90_DealerProfile_ctrl_1gyawpvos\",\"height\":\"\",\"profilefields\":\"NumberofSubmissionLocations\"}}"},{"uid":"ctrl_7k68gz4pc","data":"{\"myreports\":{\"title\":\"My Reports\",\"resourceId\":\"DashNAFTA_42_90_Report_ctrl_7k68gz4pc\",\"height\":\"\",\"count\":\"100\"}}"},{"uid":"ctrl_bdxsuhnve","data":"{\"newsflash\":{\"title\":\"Newsflash Widget\",\"resourceId\":\"DashNAFTA_42_90_News_ctrl_bdxsuhnve\",\"height\":\"\",\"count\":\"10\",\"maxwords\":\"\"}}"}]}'
--EXEC DashboardJSON '[{"uid":"ctrl_2yvnm1g77","data":"{\"kpioverview\":{\"title\":\"KPI Overview\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77\",\"height\":\"\",\"show_gap_column\":false,\"show_traffic_light\":false,\"show_Value\":false,\"Define_Orange\":\"\",\"show_benchmark_analysis\":false,\"benchmark_analysis_columns\":\"1\",\"show_trend_graph\":false,\"show_map\":false,\"filter_top1_percent\":\"\",\"filter_top2_percent\":\"\",\"filter_bottom1_percent\":\"\",\"filter_bottom2_percent\":\"\",\"kpicolumns\":{\"col1\":\"Last\",\"col2\":\"DOECountryAvg\",\"col3\":\"DOERegionAvg\",\"col4\":\"DOEVolumeGroupAvg\"},\"kpimap\":{\"hierarchy\":\"\",\"allowedallcheck\":\"true\",\"filter\":\"alldealer\"},\"kpi\":[{\"kpitext\":\"Total CNHI Parts Inventory - em % Ativo Total\",\"id\":\"assets.total.cnhi.inventories.parts.perc.value_P\",\"order\":\"0\",\"reverse_logic\":false,\"red_value\":\"100.00\",\"orange_value\":\"100.00 to 500.00\",\"green_value\":\"500.00\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77_assets.total.cnhi.inventories.parts.perc.value_P\"}]}}"},{"uid":"ctrl_1gyawpvos","data":"{\"dealerprofile\":{\"title\":\"Dealer Profile Overview\",\"resourceId\":\"DashNAFTA_42_90_DealerProfile_ctrl_1gyawpvos\",\"height\":\"\",\"profilefields\":\"NumberofSubmissionLocations\"}}"},{"uid":"ctrl_7k68gz4pc","data":"{\"myreports\":{\"title\":\"My Reports\",\"resourceId\":\"DashNAFTA_42_90_Report_ctrl_7k68gz4pc\",\"height\":\"\",\"count\":\"100\"}}"},{"uid":"ctrl_bdxsuhnve","data":"{\"newsflash\":{\"title\":\"Newsflash Widget\",\"resourceId\":\"DashNAFTA_42_90_News_ctrl_bdxsuhnve\",\"height\":\"\",\"count\":\"10\",\"maxwords\":\"\"}}"}]'
--EXEC DashboardJSON '[{"uid":"ctrl_2yvnm1g77","data":"{\"kpioverview\":{\"title\":\"KPI Overview\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77\",\"height\":\"\",\"show_gap_column\":false,\"show_traffic_light\":false,\"show_Value\":false,\"Define_Orange\":\"\",\"show_benchmark_analysis\":false,\"benchmark_analysis_columns\":\"1\",\"show_trend_graph\":false,\"show_map\":false,\"filter_top1_percent\":\"\",\"filter_top2_percent\":\"\",\"filter_bottom1_percent\":\"\",\"filter_bottom2_percent\":\"\",\"kpicolumns\":{\"col1\":\"Last\",\"col2\":\"DOECountryAvg\",\"col3\":\"DOERegionAvg\",\"col4\":\"DOEVolumeGroupAvg\"},\"kpimap\":{\"hierarchy\":\"\",\"allowedallcheck\":\"true\",\"filter\":\"alldealer\"},\"kpi\":[{\"kpitext\":\"Total CNHI Parts Inventory - em % Ativo Total\",\"id\":\"assets.total.cnhi.inventories.parts.perc.value_P\",\"order\":\"0\",\"reverse_logic\":false,\"red_value\":\"100.00\",\"orange_value\":\"100.00 to 500.00\",\"green_value\":\"500.00\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77_assets.total.cnhi.inventories.parts.perc.value_P\"}]}}"},{"uid":"ctrl_1gyawpvos","data":"{\"dealerprofile\":{\"title\":\"Dealer Profile Overview\",\"resourceId\":\"DashNAFTA_42_90_DealerProfile_ctrl_1gyawpvos\",\"height\":\"\",\"profilefields\":\"NumberofSubmissionLocations\"}}"},{"uid":"ctrl_7k68gz4pc","data":"{\"myreports\":{\"title\":\"My Reports\",\"resourceId\":\"DashNAFTA_42_90_Report_ctrl_7k68gz4pc\",\"height\":\"\",\"count\":\"100\"}}"},{"uid":"ctrl_bdxsuhnve","data":"{\"newsflash\":{\"title\":\"Newsflash Widget\",\"resourceId\":\"DashNAFTA_42_90_News_ctrl_bdxsuhnve\",\"height\":\"\",\"count\":\"10\",\"maxwords\":\"\"}}"},{"uid":"ctrl_kh4v33nzo","data":"{\"kpioverview\":{\"title\":\"KPI Overview 2\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_kh4v33nzo\",\"height\":\"700\",\"show_gap_column\":true,\"show_traffic_light\":true,\"show_Value\":true,\"Define_Orange\":\"\",\"show_benchmark_analysis\":true,\"benchmark_analysis_columns\":\"1\",\"show_trend_graph\":true,\"show_map\":false,\"filter_top1_percent\":\"\",\"filter_top2_percent\":\"\",\"filter_bottom1_percent\":\"\",\"filter_bottom2_percent\":\"\",\"kpicolumns\":{\"col1\":\"Last\",\"col2\":\"DOECountryAvg\",\"col3\":\"DOERegionAvg\",\"col4\":\"DOEVolumeGroupAvg\"},\"kpimap\":{\"hierarchy\":\"\",\"allowedallcheck\":\"true\",\"filter\":\"alldealer\"},\"kpi\":[{\"kpitext\":\"% of Parts GP for Parts Person Test\",\"id\":\"turnover.ratio.parts.gp.for.parts.compensation.value_P\",\"order\":\"0\",\"reverse_logic\":false,\"red_value\":\"1000.00\",\"orange_value\":\"1,000.00 to 5,000.00\",\"green_value\":\"5000.00\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_kh4v33nzo_turnover.ratio.parts.gp.for.parts.compensation.value_P\"},{\"kpitext\":\"<59 PTO Tractors - Actual Inventory Units\",\"id\":\"ue.det.under.59.pto.tractor.inv.unit.value.850_U\",\"order\":\"0\",\"reverse_logic\":false,\"red_value\":\"100.00\",\"orange_value\":\"100.00 to 5,000.00\",\"green_value\":\"5000.00\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_kh4v33nzo_ue.det.under.59.pto.tractor.inv.unit.value.850_U\"}]}}"},{"uid":"ctrl_x2nm3g7ef","data":"{\"kpipiechart\":{\"title\":\"KPI Pie-Chart Test\",\"resourceId\":\"DashNAFTA_42_90_PieChart_ctrl_x2nm3g7ef\",\"height\":\"\",\"kpi\":\"turnover.ratio.parts.gp.for.parts.compensation.value,turnover.ratio.wholegoods.gp.for.sales.compensation.value\"}}"}]'
ALTER PROCEDURE [dbo].[DashboardJSON] @json nvarchar(max)
AS
BEGIN
Declare @JSONValue nvarchar(max)
Declare @JSONValueMain nvarchar(max)
Declare @JSONValue1 nvarchar(max)
Declare @ID nvarchar(max), @Value nvarchar(max)
Declare @Localization as Table (ID nvarchar(max), Value nvarchar(max))
SET @json = '{"MainData":' + @json + '}'
DECLARE JSONDataMain CURSOR FOR
SELECT Value FROM OPENJSON(@json,'$.MainData') --where [key]='data'
OPEN JSONDataMain
FETCH NEXT FROM JSONDataMain INTO @JSONValueMain
WHILE @@FETCH_STATUS = 0
BEGIN
------------------------
SET @JSONValueMain = '{"MainData":' + @JSONValueMain + '}'
DECLARE JSONData CURSOR FOR
SELECT Value FROM OPENJSON(@JSONValueMain,'$.MainData') where [key]='data'
OPEN JSONData
FETCH NEXT FROM JSONData INTO @JSONValue
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ID = NULL
SET @Value = NULL
select @ID = Value from OPENJSON(@JSONValue, '$.kpioverview') Where [key]='resourceId'
select @Value = Value from OPENJSON(@JSONValue, '$.kpioverview') Where [key]='title'
IF @ID IS NOT NULL and @Value is not null
Insert into @Localization Select @ID, @Value
Declare @i int = 0
DECLARE JSONData1 CURSOR FOR
select Value FROM OPENJSON(@JSONValue,'$.kpioverview.kpi')
OPEN JSONData1
FETCH NEXT FROM JSONData1 INTO @JSONValue1
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @sql nvarchar(max)
SET @ID = NULL
SET @Value = NULL
--set @sql = 'Select Value from OPENJSON(' + @JSONValue1 + ', ''$.kpioverview.kpi[' + Convert(varchar(5),@i) + '']''') Where [key]=''kpitext'''
set @sql = ' select @ID = Value FROM OPENJSON(''' + @JSONValue + ''' ,''$.kpioverview.kpi[' + Convert(varchar(5),@i) + ']'') Where [key]=''resourceId'''
exec sp_executeSQl @sql, N'@ID nvarchar(max) output', @ID output
set @sql = replace(replace(@sql,'resourceId','kpitext'),'@ID','@Value')
exec sp_executeSQl @sql, N'@Value nvarchar(max) output', @Value output
IF @ID IS NOT NULL and @Value is not null
Insert into @Localization Select @ID, @Value
SET @i = @i + 1
FETCH NEXT FROM JSONData1 INTO @JSONValue1
END
ClOSE JSONData1
DEALLOCATE JSONData1
SET @ID = NULL
SET @Value = NULL
select @ID = Value from OPENJSON(@JSONValue, '$.dealerprofile') Where [key]='resourceId'
select @Value = Value from OPENJSON(@JSONValue, '$.dealerprofile') Where [key]='title'
IF @ID IS NOT NULL and @Value is not null
Insert into @Localization Select @ID, @Value
SET @ID = NULL
SET @Value = NULL
select @ID = Value from OPENJSON(@JSONValue, '$.myreports') Where [key]='resourceId'
select @Value = Value from OPENJSON(@JSONValue, '$.myreports') Where [key]='title'
IF @ID IS NOT NULL and @Value is not null
Insert into @Localization Select @ID, @Value
SET @ID = NULL
SET @Value = NULL
select @ID = Value from OPENJSON(@JSONValue, '$.newsflash') Where [key]='resourceId'
select @Value = Value from OPENJSON(@JSONValue, '$.newsflash') Where [key]='title'
IF @ID IS NOT NULL and @Value is not null
Insert into @Localization Select @ID, @Value
SET @ID = NULL
SET @Value = NULL
select @ID = Value from OPENJSON(@JSONValue, '$.kpipiechart') Where [key]='resourceId'
select @Value = Value from OPENJSON(@JSONValue, '$.kpipiechart') Where [key]='title'
IF @ID IS NOT NULL and @Value is not null
Insert into @Localization Select @ID, @Value
FETCH NEXT FROM JSONData INTO @JSONValue
END
ClOSE JSONData
DEALLOCATE JSONData
FETCH NEXT FROM JSONDataMain INTO @JSONValueMain
END
ClOSE JSONDataMain
DEALLOCATE JSONDataMain
Select ID ResourceID, Value From @Localization
--SELECT Value
--FROM OPENJSON(@json,'$.Employees[0]') where [key]='data'
--declare @json2 nvarchar(max)='{"kpioverview":{"title":"KPI Overview","resourceId":"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77","height":"","show_gap_column":false,"show_traffic_light":false,"show_Value":false,"Define_Orange":"","show_benchmark_analysis":false,"benchmark_analysis_columns":"1","show_trend_graph":false,"show_map":false,"filter_top1_percent":"","filter_top2_percent":"","filter_bottom1_percent":"","filter_bottom2_percent":"","kpicolumns":{"col1":"Last","col2":"DOECountryAvg","col3":"DOERegionAvg","col4":"DOEVolumeGroupAvg"},"kpimap":{"hierarchy":"","allowedallcheck":"true","filter":"alldealer"},"kpi":[{"kpitext":"Total CNHI Parts Inventory - em % Ativo Total","id":"assets.total.cnhi.inventories.parts.perc.value_P","order":"0","reverse_logic":false,"red_value":"100.00","orange_value":"100.00 to 500.00","green_value":"500.00","resourceId":"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77_assets.total.cnhi.inventories.parts.perc.value_P"}]}}'
--select * FROM OPENJSON('{"kpioverview":{"title":"KPI Overview","resourceId":"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77","height":"","show_gap_column":false,"show_traffic_light":false,"show_Value":false,"Define_Orange":"","show_benchmark_analysis":false,"benchmark_analysis_columns":"1","show_trend_graph":false,"show_map":false,"filter_top1_percent":"","filter_top2_percent":"","filter_bottom1_percent":"","filter_bottom2_percent":"","kpicolumns":{"col1":"Last","col2":"DOECountryAvg","col3":"DOERegionAvg","col4":"DOEVolumeGroupAvg"},"kpimap":{"hierarchy":"","allowedallcheck":"true","filter":"alldealer"},"kpi":[{"kpitext":"Total CNHI Parts Inventory - em % Ativo Total","id":"assets.total.cnhi.inventories.parts.perc.value_P","order":"0","reverse_logic":false,"red_value":"100.00","orange_value":"100.00 to 500.00","green_value":"500.00","resourceId":"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77_assets.total.cnhi.inventories.parts.perc.value_P"}]}}','$.kpioverview.kpi[0]')
--select Value FROM OPENJSON('{"kpitext":"Total CNHI Parts Inventory - em % Ativo Total","id":"assets.total.cnhi.inventories.parts.perc.value_P","order":"0","reverse_logic":false,"red_value":"100.00","orange_value":"100.00 to 500.00","green_value":"500.00","resourceId":"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77_assets.total.cnhi.inventories.parts.perc.value_P"}' ,'$.kpioverview.kpi[0]') Where [key]='resourceId'
END
http://jsonviewer.stack.hu/
https://stackoverflow.com/questions/37218254/sql-server-openjson-read-nested-json?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa
declare @json nvarchar(max)
set @json = '
[
{
"IdProject":"97A76363-095D-4FAB-940E-9ED2722DBC47",
"Name":"Test Project",
"structures":[
{
"IdStructure":"CB0466F9-662F-412B-956A-7D164B5D358F",
"IdProject":"97A76363-095D-4FAB-940E-9ED2722DBC47",
"Name":"Test Structure",
"BaseStructure":"Base Structure",
"DatabaseSchema":"dbo",
"properties":[
{
"IdProperty":"618DC40B-4D04-4BF8-B1E6-12E13DDE86F4",
"IdStructure":"CB0466F9-662F-412B-956A-7D164B5D358F",
"Name":"Test Property 2",
"DataType":1,
"Precision":0,
"Scale":0,
"IsNullable":false,
"ObjectName":"Test Object",
"DefaultType":1,
"DefaultValue":""
},
{
"IdProperty":"FFF433EC-0BB5-41CD-8A71-B5F09B97C5FC",
"IdStructure":"CB0466F9-662F-412B-956A-7D164B5D358F",
"Name":"Test Property 1",
"DataType":1,
"Precision":0,
"Scale":0,
"IsNullable":false,
"ObjectName":"Test Object",
"DefaultType":1,
"DefaultValue":""
}
]
}
]
}
]';
select
Projects.IdProject, Projects.Name as NameProject,
Structures.IdStructure, Structures.Name as NameStructure, Structures.BaseStructure, Structures.DatabaseSchema,
Properties.*
from openjson (@json)
with
(
IdProject uniqueidentifier,
Name nvarchar(100),
structures nvarchar(max) as json
)
as Projects
cross apply openjson (Projects.structures)
with
(
IdStructure uniqueidentifier,
Name nvarchar(100),
BaseStructure nvarchar(100),
DatabaseSchema sysname,
properties nvarchar(max) as json
) as Structures
cross apply openjson (Structures.properties)
with
(
IdProperty uniqueidentifier,
NamePreoperty nvarchar(100) '$.Name',
DataType int,
[Precision] int,
[Scale] int,
IsNullable bit,
ObjectName nvarchar(100),
DefaultType int,
DefaultValue nvarchar(100)
)
as Properties
************************************
USE [JS]
GO
/****** Object: StoredProcedure [dbo].[DashboardJSON] Script Date: 5/10/2018 6:07:27 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Naveen Gupta
-- Create date: 07-May-2018
-- Description: To Find out the relevant values for Localization table from Dashboard JSON
-- =============================================
--EXEC DashboardJSON '{"Employees":[{"uid":"ctrl_2yvnm1g77","data":"{\"kpioverview\":{\"title\":\"KPI Overview\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77\",\"height\":\"\",\"show_gap_column\":false,\"show_traffic_light\":false,\"show_Value\":false,\"Define_Orange\":\"\",\"show_benchmark_analysis\":false,\"benchmark_analysis_columns\":\"1\",\"show_trend_graph\":false,\"show_map\":false,\"filter_top1_percent\":\"\",\"filter_top2_percent\":\"\",\"filter_bottom1_percent\":\"\",\"filter_bottom2_percent\":\"\",\"kpicolumns\":{\"col1\":\"Last\",\"col2\":\"DOECountryAvg\",\"col3\":\"DOERegionAvg\",\"col4\":\"DOEVolumeGroupAvg\"},\"kpimap\":{\"hierarchy\":\"\",\"allowedallcheck\":\"true\",\"filter\":\"alldealer\"},\"kpi\":[{\"kpitext\":\"Total CNHI Parts Inventory - em % Ativo Total\",\"id\":\"assets.total.cnhi.inventories.parts.perc.value_P\",\"order\":\"0\",\"reverse_logic\":false,\"red_value\":\"100.00\",\"orange_value\":\"100.00 to 500.00\",\"green_value\":\"500.00\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77_assets.total.cnhi.inventories.parts.perc.value_P\"}]}}"},{"uid":"ctrl_1gyawpvos","data":"{\"dealerprofile\":{\"title\":\"Dealer Profile Overview\",\"resourceId\":\"DashNAFTA_42_90_DealerProfile_ctrl_1gyawpvos\",\"height\":\"\",\"profilefields\":\"NumberofSubmissionLocations\"}}"},{"uid":"ctrl_7k68gz4pc","data":"{\"myreports\":{\"title\":\"My Reports\",\"resourceId\":\"DashNAFTA_42_90_Report_ctrl_7k68gz4pc\",\"height\":\"\",\"count\":\"100\"}}"},{"uid":"ctrl_bdxsuhnve","data":"{\"newsflash\":{\"title\":\"Newsflash Widget\",\"resourceId\":\"DashNAFTA_42_90_News_ctrl_bdxsuhnve\",\"height\":\"\",\"count\":\"10\",\"maxwords\":\"\"}}"}]}'
--EXEC DashboardJSON '[{"uid":"ctrl_2yvnm1g77","data":"{\"kpioverview\":{\"title\":\"KPI Overview\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77\",\"height\":\"\",\"show_gap_column\":false,\"show_traffic_light\":false,\"show_Value\":false,\"Define_Orange\":\"\",\"show_benchmark_analysis\":false,\"benchmark_analysis_columns\":\"1\",\"show_trend_graph\":false,\"show_map\":false,\"filter_top1_percent\":\"\",\"filter_top2_percent\":\"\",\"filter_bottom1_percent\":\"\",\"filter_bottom2_percent\":\"\",\"kpicolumns\":{\"col1\":\"Last\",\"col2\":\"DOECountryAvg\",\"col3\":\"DOERegionAvg\",\"col4\":\"DOEVolumeGroupAvg\"},\"kpimap\":{\"hierarchy\":\"\",\"allowedallcheck\":\"true\",\"filter\":\"alldealer\"},\"kpi\":[{\"kpitext\":\"Total CNHI Parts Inventory - em % Ativo Total\",\"id\":\"assets.total.cnhi.inventories.parts.perc.value_P\",\"order\":\"0\",\"reverse_logic\":false,\"red_value\":\"100.00\",\"orange_value\":\"100.00 to 500.00\",\"green_value\":\"500.00\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77_assets.total.cnhi.inventories.parts.perc.value_P\"}]}}"},{"uid":"ctrl_1gyawpvos","data":"{\"dealerprofile\":{\"title\":\"Dealer Profile Overview\",\"resourceId\":\"DashNAFTA_42_90_DealerProfile_ctrl_1gyawpvos\",\"height\":\"\",\"profilefields\":\"NumberofSubmissionLocations\"}}"},{"uid":"ctrl_7k68gz4pc","data":"{\"myreports\":{\"title\":\"My Reports\",\"resourceId\":\"DashNAFTA_42_90_Report_ctrl_7k68gz4pc\",\"height\":\"\",\"count\":\"100\"}}"},{"uid":"ctrl_bdxsuhnve","data":"{\"newsflash\":{\"title\":\"Newsflash Widget\",\"resourceId\":\"DashNAFTA_42_90_News_ctrl_bdxsuhnve\",\"height\":\"\",\"count\":\"10\",\"maxwords\":\"\"}}"}]'
--EXEC DashboardJSON '[{"uid":"ctrl_2yvnm1g77","data":"{\"kpioverview\":{\"title\":\"KPI Overview\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77\",\"height\":\"\",\"show_gap_column\":false,\"show_traffic_light\":false,\"show_Value\":false,\"Define_Orange\":\"\",\"show_benchmark_analysis\":false,\"benchmark_analysis_columns\":\"1\",\"show_trend_graph\":false,\"show_map\":false,\"filter_top1_percent\":\"\",\"filter_top2_percent\":\"\",\"filter_bottom1_percent\":\"\",\"filter_bottom2_percent\":\"\",\"kpicolumns\":{\"col1\":\"Last\",\"col2\":\"DOECountryAvg\",\"col3\":\"DOERegionAvg\",\"col4\":\"DOEVolumeGroupAvg\"},\"kpimap\":{\"hierarchy\":\"\",\"allowedallcheck\":\"true\",\"filter\":\"alldealer\"},\"kpi\":[{\"kpitext\":\"Total CNHI Parts Inventory - em % Ativo Total\",\"id\":\"assets.total.cnhi.inventories.parts.perc.value_P\",\"order\":\"0\",\"reverse_logic\":false,\"red_value\":\"100.00\",\"orange_value\":\"100.00 to 500.00\",\"green_value\":\"500.00\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77_assets.total.cnhi.inventories.parts.perc.value_P\"}]}}"},{"uid":"ctrl_1gyawpvos","data":"{\"dealerprofile\":{\"title\":\"Dealer Profile Overview\",\"resourceId\":\"DashNAFTA_42_90_DealerProfile_ctrl_1gyawpvos\",\"height\":\"\",\"profilefields\":\"NumberofSubmissionLocations\"}}"},{"uid":"ctrl_7k68gz4pc","data":"{\"myreports\":{\"title\":\"My Reports\",\"resourceId\":\"DashNAFTA_42_90_Report_ctrl_7k68gz4pc\",\"height\":\"\",\"count\":\"100\"}}"},{"uid":"ctrl_bdxsuhnve","data":"{\"newsflash\":{\"title\":\"Newsflash Widget\",\"resourceId\":\"DashNAFTA_42_90_News_ctrl_bdxsuhnve\",\"height\":\"\",\"count\":\"10\",\"maxwords\":\"\"}}"},{"uid":"ctrl_kh4v33nzo","data":"{\"kpioverview\":{\"title\":\"KPI Overview 2\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_kh4v33nzo\",\"height\":\"700\",\"show_gap_column\":true,\"show_traffic_light\":true,\"show_Value\":true,\"Define_Orange\":\"\",\"show_benchmark_analysis\":true,\"benchmark_analysis_columns\":\"1\",\"show_trend_graph\":true,\"show_map\":false,\"filter_top1_percent\":\"\",\"filter_top2_percent\":\"\",\"filter_bottom1_percent\":\"\",\"filter_bottom2_percent\":\"\",\"kpicolumns\":{\"col1\":\"Last\",\"col2\":\"DOECountryAvg\",\"col3\":\"DOERegionAvg\",\"col4\":\"DOEVolumeGroupAvg\"},\"kpimap\":{\"hierarchy\":\"\",\"allowedallcheck\":\"true\",\"filter\":\"alldealer\"},\"kpi\":[{\"kpitext\":\"% of Parts GP for Parts Person Test\",\"id\":\"turnover.ratio.parts.gp.for.parts.compensation.value_P\",\"order\":\"0\",\"reverse_logic\":false,\"red_value\":\"1000.00\",\"orange_value\":\"1,000.00 to 5,000.00\",\"green_value\":\"5000.00\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_kh4v33nzo_turnover.ratio.parts.gp.for.parts.compensation.value_P\"},{\"kpitext\":\"<59 PTO Tractors - Actual Inventory Units\",\"id\":\"ue.det.under.59.pto.tractor.inv.unit.value.850_U\",\"order\":\"0\",\"reverse_logic\":false,\"red_value\":\"100.00\",\"orange_value\":\"100.00 to 5,000.00\",\"green_value\":\"5000.00\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_kh4v33nzo_ue.det.under.59.pto.tractor.inv.unit.value.850_U\"}]}}"},{"uid":"ctrl_x2nm3g7ef","data":"{\"kpipiechart\":{\"title\":\"KPI Pie-Chart Test\",\"resourceId\":\"DashNAFTA_42_90_PieChart_ctrl_x2nm3g7ef\",\"height\":\"\",\"kpi\":\"turnover.ratio.parts.gp.for.parts.compensation.value,turnover.ratio.wholegoods.gp.for.sales.compensation.value\"}}"}]'
ALTER PROCEDURE [dbo].[DashboardJSON] @json nvarchar(max)
AS
BEGIN
Declare @JSONValue nvarchar(max)
Declare @JSONValueMain nvarchar(max)
Declare @JSONValue1 nvarchar(max)
Declare @ID nvarchar(max), @Value nvarchar(max)
Declare @Localization as Table (ID nvarchar(max), Value nvarchar(max))
SET @json = '{"MainData":' + @json + '}'
DECLARE JSONDataMain CURSOR FOR
SELECT Value FROM OPENJSON(@json,'$.MainData') --where [key]='data'
OPEN JSONDataMain
FETCH NEXT FROM JSONDataMain INTO @JSONValueMain
WHILE @@FETCH_STATUS = 0
BEGIN
------------------------
SET @JSONValueMain = '{"MainData":' + @JSONValueMain + '}'
DECLARE JSONData CURSOR FOR
SELECT Value FROM OPENJSON(@JSONValueMain,'$.MainData') where [key]='data'
OPEN JSONData
FETCH NEXT FROM JSONData INTO @JSONValue
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ID = NULL
SET @Value = NULL
select @ID = Value from OPENJSON(@JSONValue, '$.kpioverview') Where [key]='resourceId'
select @Value = Value from OPENJSON(@JSONValue, '$.kpioverview') Where [key]='title'
IF @ID IS NOT NULL and @Value is not null
Insert into @Localization Select @ID, @Value
Declare @i int = 0
DECLARE JSONData1 CURSOR FOR
select Value FROM OPENJSON(@JSONValue,'$.kpioverview.kpi')
OPEN JSONData1
FETCH NEXT FROM JSONData1 INTO @JSONValue1
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @sql nvarchar(max)
SET @ID = NULL
SET @Value = NULL
--set @sql = 'Select Value from OPENJSON(' + @JSONValue1 + ', ''$.kpioverview.kpi[' + Convert(varchar(5),@i) + '']''') Where [key]=''kpitext'''
set @sql = ' select @ID = Value FROM OPENJSON(''' + @JSONValue + ''' ,''$.kpioverview.kpi[' + Convert(varchar(5),@i) + ']'') Where [key]=''resourceId'''
exec sp_executeSQl @sql, N'@ID nvarchar(max) output', @ID output
set @sql = replace(replace(@sql,'resourceId','kpitext'),'@ID','@Value')
exec sp_executeSQl @sql, N'@Value nvarchar(max) output', @Value output
IF @ID IS NOT NULL and @Value is not null
Insert into @Localization Select @ID, @Value
SET @i = @i + 1
FETCH NEXT FROM JSONData1 INTO @JSONValue1
END
ClOSE JSONData1
DEALLOCATE JSONData1
SET @ID = NULL
SET @Value = NULL
select @ID = Value from OPENJSON(@JSONValue, '$.dealerprofile') Where [key]='resourceId'
select @Value = Value from OPENJSON(@JSONValue, '$.dealerprofile') Where [key]='title'
IF @ID IS NOT NULL and @Value is not null
Insert into @Localization Select @ID, @Value
SET @ID = NULL
SET @Value = NULL
select @ID = Value from OPENJSON(@JSONValue, '$.myreports') Where [key]='resourceId'
select @Value = Value from OPENJSON(@JSONValue, '$.myreports') Where [key]='title'
IF @ID IS NOT NULL and @Value is not null
Insert into @Localization Select @ID, @Value
SET @ID = NULL
SET @Value = NULL
select @ID = Value from OPENJSON(@JSONValue, '$.newsflash') Where [key]='resourceId'
select @Value = Value from OPENJSON(@JSONValue, '$.newsflash') Where [key]='title'
IF @ID IS NOT NULL and @Value is not null
Insert into @Localization Select @ID, @Value
SET @ID = NULL
SET @Value = NULL
select @ID = Value from OPENJSON(@JSONValue, '$.kpipiechart') Where [key]='resourceId'
select @Value = Value from OPENJSON(@JSONValue, '$.kpipiechart') Where [key]='title'
IF @ID IS NOT NULL and @Value is not null
Insert into @Localization Select @ID, @Value
FETCH NEXT FROM JSONData INTO @JSONValue
END
ClOSE JSONData
DEALLOCATE JSONData
FETCH NEXT FROM JSONDataMain INTO @JSONValueMain
END
ClOSE JSONDataMain
DEALLOCATE JSONDataMain
Select ID ResourceID, Value From @Localization
--SELECT Value
--FROM OPENJSON(@json,'$.Employees[0]') where [key]='data'
--declare @json2 nvarchar(max)='{"kpioverview":{"title":"KPI Overview","resourceId":"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77","height":"","show_gap_column":false,"show_traffic_light":false,"show_Value":false,"Define_Orange":"","show_benchmark_analysis":false,"benchmark_analysis_columns":"1","show_trend_graph":false,"show_map":false,"filter_top1_percent":"","filter_top2_percent":"","filter_bottom1_percent":"","filter_bottom2_percent":"","kpicolumns":{"col1":"Last","col2":"DOECountryAvg","col3":"DOERegionAvg","col4":"DOEVolumeGroupAvg"},"kpimap":{"hierarchy":"","allowedallcheck":"true","filter":"alldealer"},"kpi":[{"kpitext":"Total CNHI Parts Inventory - em % Ativo Total","id":"assets.total.cnhi.inventories.parts.perc.value_P","order":"0","reverse_logic":false,"red_value":"100.00","orange_value":"100.00 to 500.00","green_value":"500.00","resourceId":"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77_assets.total.cnhi.inventories.parts.perc.value_P"}]}}'
--select * FROM OPENJSON('{"kpioverview":{"title":"KPI Overview","resourceId":"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77","height":"","show_gap_column":false,"show_traffic_light":false,"show_Value":false,"Define_Orange":"","show_benchmark_analysis":false,"benchmark_analysis_columns":"1","show_trend_graph":false,"show_map":false,"filter_top1_percent":"","filter_top2_percent":"","filter_bottom1_percent":"","filter_bottom2_percent":"","kpicolumns":{"col1":"Last","col2":"DOECountryAvg","col3":"DOERegionAvg","col4":"DOEVolumeGroupAvg"},"kpimap":{"hierarchy":"","allowedallcheck":"true","filter":"alldealer"},"kpi":[{"kpitext":"Total CNHI Parts Inventory - em % Ativo Total","id":"assets.total.cnhi.inventories.parts.perc.value_P","order":"0","reverse_logic":false,"red_value":"100.00","orange_value":"100.00 to 500.00","green_value":"500.00","resourceId":"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77_assets.total.cnhi.inventories.parts.perc.value_P"}]}}','$.kpioverview.kpi[0]')
--select Value FROM OPENJSON('{"kpitext":"Total CNHI Parts Inventory - em % Ativo Total","id":"assets.total.cnhi.inventories.parts.perc.value_P","order":"0","reverse_logic":false,"red_value":"100.00","orange_value":"100.00 to 500.00","green_value":"500.00","resourceId":"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77_assets.total.cnhi.inventories.parts.perc.value_P"}' ,'$.kpioverview.kpi[0]') Where [key]='resourceId'
END
No comments:
Post a Comment