Generate the WITH part for the OPENJSON() function

This simple script helps you prepare the WITH statement part for the OPENJSON() function. For large JSON data it will scan all nodes and build a distinct list of all available elements. You can adjust it for your needs (different nesting level etc.).

DECLARE @JSON NVARCHAR(MAX)

SET @JSON = 
N'{
	"Employees": [
		{
			"Id": 1,
			"FirstName": "John",
			"LastName": "Doe"
		},
		{
			"Id": 2,
			"FirstName": "Paula",
			"LastName": "Klein",
			"BornName": "Smith",
			"Address": "Cross road 123, NY",
			"Married": true,
			"Kids": ["Ema", "Nick"]
		}
	]
}'

SELECT  
	DISTINCT '[' + [g].[Key] + '] ' + CASE [g].[Type]
						WHEN 1 THEN 'NVARCHAR(4000)'
						WHEN 2 THEN 'INT'
						WHEN 3 THEN 'BIT'
						WHEN 4 THEN 'NVARCHAR(MAX) AS JSON'
						WHEN 5 THEN 'NVARCHAR(MAX) AS JSON'
						ELSE 'X'
					END + ' ''$.' + [g].[Key] + ''','
FROM OPENJSON(@JSON) [e]
	CROSS APPLY OPENJSON([e].[Value]) [f]
	CROSS APPLY OPENJSON([f].[Value]) [g]
ORDER BY 1

SELECT 
	[g].*
FROM OPENJSON(@JSON) [e]
	CROSS APPLY OPENJSON([e].[Value]) [f]
	CROSS APPLY OPENJSON([f].[Value])
	WITH (
		[Address]	NVARCHAR(4000)	'$.Address',
		[BornName]	NVARCHAR(4000)	'$.BornName',
		[FirstName]	NVARCHAR(4000)	'$.FirstName',
		[Id]		INT		'$.Id',
		[LastName]	NVARCHAR(4000)	'$.LastName',
		[Married]	BIT		'$.Married',
		[Kids]		NVARCHAR(MAX)	'$.Kids' AS JSON
	) [g]
GO