Transforming AppSettings.json to Azure Configuration Settings

Overview

This article provides code to transform the JSON found in an "appsettings.json" file to the JSON using by Azure Configuration settings.

Code

declare @input varchar(1000) = '
{ // be sure to add this
	"SSRS": {
		"UserName": "MySsrsUsername",
		"Password": "MySsrsPassword",
		"RootURL": "http:/myserver.eastus.cloudapp.azure.com/ReportServer?"
	},
	"PowerBI": {
		"ApplicationId": "dd9da583-8db3-430b-bfd9-3df1c355ef6d",
		"ApplicationSecret": "MyAppSecret",
		"WorkspaceId": "b4f1d20c-8914-453b-a666-ee5804d9463b",
		"UserName": "MyPowerBiUserName",
		"Password": "MyPowerBiPassword"
	}
} // be sure to add this
  '

;with cte as (
	select 
		 Key1 = keys.[key]
		,Key2 = vals.[key]
	from 
		openjson(@input) keys
		cross apply (
			select b.[key] from openjson(keys.[value]) b
		) vals
	)
,MyKeys as (
	select
		 JsonKey = '$.' + Key1 + '.' + Key2
		,AzureKey = Key1 + ':' + Key2
	from
		cte
	)
,NameValuePairs as (
	select
		AzureKey
		,[Value] = JSON_VALUE(@input, JsonKey)
	from
		MyKeys
	)
select
	 AzureKey
	,Sort2		= 10
	,Json		= '{'
from
	NameValuePairs
union select
	 AzureKey
	,Sort2		= 20
	,Json		= '  "name": "' + AzureKey + '",'
from
	NameValuePairs
union select
	 AzureKey
	,Sort2		= 30
	,Json		= '  "value": "' + [Value] + '",'
from
	NameValuePairs
union select
	 AzureKey
	,Sort2		= 40
	,Json		= '  "slotSetting": false'
from
	NameValuePairs
union select
	 AzureKey
	,Sort2		= 50
	,Json		= '},'
from
	NameValuePairs
order by AzureKey, Sort2