SQL from JSON generator

gistfile1.txt

/*
This code takes a JSON input string and automatically generates
SQL Server CREATE TABLE statements to make it easier
to convert serialized data into a database schema.

It is not perfect, but should provide a decent starting point when starting
to work with new JSON files.

A blog post with more information can be found at https://bertwagner.com/2018/05/22/converting-json-to-sql-server-create-table-statements/
*/
SET NOCOUNT ON;

DECLARE 
	@JsonData nvarchar(max) = '
		{
	"version": "RBox, Version=1.5.2.4, Culture=neutral, PublicKeyToken=null",
	"request": {
		"userId": "Someone",
		"productType": "PayDay",
		"applicationId": "3407900",
		"requestId": "140286",
		"timestamp": "2020-02-24T16:00:00",
		"test": false,
		"applicationForm": {
			"firstName": "Jan",
			"lastName": "Kowalski",
			"bankAccountNumber": "PL00 0000 0000 0000 0000 0000 0000",
			"pesel": 75010123456,
			"documentId": "ABC123456",
			"birthDate": "1975-01-01T00:00:00",
			"gender": "M",
			"requestedAmount": 2000,
			"requestedTenor": 1,
			"applicationSource": "NULL",
			"incomeSource": "Umowa o pracę/zlecenie",
			"nip": 0,
			"job": "POCZTA POLSKA S.A.",
			"employmentSegment": "NULL",
			"purpose": "NULL",
			"salaryDay": 15,
			"jobExperienceYears": 0,
			"jobExperienceMonths": 0,
			"income": 2000,
			"incomeHousehold": 0,
			"installmentAmount": 0,
			"limitAmount": 0,
			"expenses": 0,
			"householdMembers": 0,
			"maritalStatus": "ŻONATY\/ZAMĘŻNA",
			"education": "ŚREDNIE",
			"housingStatus": "NULL",
			"housingYears": 0,
			"housingMonths": 0,
			"car": true,
			"carYear": 2020
		},
		"internalPerformance": {
			"lastApplication": null,
			"lastApplicationStatus": "NULL",
			"isAlreadyClient": true,
			"maxDPD": 0,
			"prevMaxDPD": 0,
			"histCount": 1,
			"prevAmount": 0,
			"isActive": false,
			"hasRecentBIK": false,
			"screenScrapeType": "Instantor"
		},
		"dataToSkip": {
			"screenScrape": false,
			"blueCash": true,
			"erif": true,
			"krd": true,
			"infoMonitor": true,
			"bik": true,
			"crif": false
		}
	},
	"responseTime": "2020-02-24T15:00:00",
	"riskGroup": null,
	"grantedProductType": null,
	"decision": {
		"code": 3,
		"report": "Final",
		"reason": 39300,
		"descriptionReason": "Criteria met",
		"descriptionClient": "Criteria met"
	},
	"grantedAmount": 2000,
	"grantedAmountMax": 5000,
	"clientAge": 45,
	"dti": 0,
	"grantedTenor": 1,
	"decisionScore": 0.0,
	"bikData": null,
	"crifData": {
		"flag": "GREEN",
		"clientExtensions": {
			"swo.ind.loans_number_P91_210": "35"
		}
	},
	"otherBureaus": null,
	"blueCash": null,
	"screenScrape": {
		"reportDate": "2019-01-19T09:28:45.546+00:00",
		"type": "Instantor",
		"names": [
			"Jan Kowalski"
		],
		"pesel": null,
		"birthDate": null,
		"oldestActivity": "2020-02-01T00:00:00",
		"oldestActivityMonths": 0,
		"activeSinceAtLeast": "2020-02-01T00:00:00",
		"activeSinceAtLeastMonths": 0,
		"bankAccountNumbers": [
			"00000000000000000000000000"
		],
		"verif": [
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0
		],
		"extAmt": [
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0
		],
		"extN": [
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0
		],
		"disbAmt": [
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0
		],
		"disbN": [
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0
		],
		"pmtAmt": [
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			-350
		],
		"pmtN": [
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			1
		],
		"colN": [
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0
		],
		"colAmt": [
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0
		],
		"incomeAmt": [
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0
		],
		"incomeN": [
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0
		],
		"inflowCleared": [
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			1100
		],
		"inflow": [
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			1100
		],
		"outflow": [
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			-2369
		],
		"gamblingAmt": [
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0
		],
		"gamblingN": [
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0,
			0
		],
		"newAccounts": 0
	},
	"progress": {
		"krdIsOk": null,
		"erifIsOk": null,
		"imIsOk": null,
		"bikIsOk": null,
		"screenScrapeIsOk": true,
		"crifIsOk": true,
		"blueCashIsOk": null
	},
	"amtMatrix": 5000
}
',
	@RootTableName nvarchar(MAX) = N'RboxRequest',
	@Schema nvarchar(128) = N'dbo',
	@DefaultStringPadding smallint = 20;

DROP TABLE IF EXISTS ##parsedJson;
WITH jsonRoot AS (
	SELECT 
		0 as parentLevel, 
		CONVERT(nvarchar(MAX),NULL) COLLATE Latin1_General_BIN2 as parentTableName, 
		0 AS [level], 
		[type] ,
		@RootTableName COLLATE Latin1_General_BIN2 AS TableName,
		[key] COLLATE Latin1_General_BIN2 as ColumnName,
		[value],
		ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ColumnSequence
	FROM 
		OPENJSON(@JsonData, '$')
	UNION ALL
	SELECT 
		jsonRoot.[level] as parentLevel, 
		CONVERT(nvarchar(MAX),jsonRoot.TableName) COLLATE Latin1_General_BIN2, 
		jsonRoot.[level]+1, 
		d.[type],
		CASE WHEN jsonRoot.[type] IN (4,5) THEN CONVERT(nvarchar(MAX),jsonRoot.ColumnName) ELSE jsonRoot.TableName END COLLATE Latin1_General_BIN2,
		CASE WHEN jsonRoot.[type] IN (4) THEN jsonRoot.ColumnName ELSE d.[key] END,
		d.[value],
		ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ColumnSequence
	FROM 
		jsonRoot
		CROSS APPLY OPENJSON(jsonRoot.[value], '$') d
	WHERE 
		jsonRoot.[type] IN (4,5) 
), IdRows AS (
	SELECT 
		-2 as parentLevel,
		null as parentTableName,
		-1 as [level],
		null as [type],
		TableName as Tablename,
		TableName+'Id' as columnName, 
		null as [value],
		0 as columnsequence
	FROM 
		(SELECT DISTINCT tablename FROM jsonRoot) j
), FKRows AS (
	SELECT 
		DISTINCT -1 as parentLevel,
		null as parentTableName,
		-1 as [level],
		null as [type],
		TableName as Tablename,
		parentTableName+'Id' as columnName, 
		null as [value],
		0 as columnsequence
	FROM 
		(SELECT DISTINCT tableName,parentTableName FROM jsonRoot) j
	WHERE 
		parentTableName is not null
)
SELECT 
	*,
	CASE [type]
		WHEN 1 THEN 
			CASE WHEN TRY_CONVERT(datetime2, [value], 127) IS NULL THEN 'nvarchar' ELSE 'datetime2' END
		WHEN 2 THEN 
			CASE WHEN TRY_CONVERT(int, [value]) IS NULL THEN 'float' ELSE 'int' END
		WHEN 3 THEN 
			'bit'
		END COLLATE Latin1_General_BIN2 AS DataType,
	CASE [type]
		WHEN 1 THEN 
			CASE WHEN TRY_CONVERT(datetime2, [value], 127) IS NULL THEN MAX(LEN([value])) OVER (PARTITION BY TableName, ColumnName) + @DefaultStringPadding ELSE NULL END
		WHEN 2 THEN 
			NULL
		WHEN 3 THEN 
			NULL
		END AS DataTypePrecision
INTO ##parsedJson
FROM jsonRoot
WHERE 
	[type] in (1,2,3)
UNION ALL SELECT IdRows.parentLevel, IdRows.parentTableName, IdRows.[level], IdRows.[type], IdRows.TableName, IdRows.ColumnName, IdRows.[value], -10 AS ColumnSequence, 'int IDENTITY(1,1) PRIMARY KEY' as datatype, null as datatypeprecision FROM IdRows 
UNION ALL SELECT FKRows.parentLevel, FKRows.parentTableName, FKRows.[level], FKRows.[type], FKRows.TableName, FKRows.ColumnName, FKRows.[value], -9 AS ColumnSequence, ('int FOREIGN KEY REFERENCES ' + SUBSTRING(FKRows.ColumnName, 0, LEN(FKRows.ColumnName)-1) + '(' + FKRows.ColumnName + ')') as datatype, null as datatypeprecision FROM FKRows 

-- For debugging:
-- SELECT * FROM ##parsedJson ORDER BY ParentLevel, level, tablename, columnsequence

DECLARE @CreateStatements nvarchar(MAX);

SELECT ( CHAR(13) + CHAR(13) +
	'CREATE TABLE ' + @Schema + '.' + TableName + CHAR(13) + '(' + CHAR(13) +
		STRING_AGG( ColumnName + ' ' + DataType + ISNULL('('+CAST(DataTypePrecision AS nvarchar(20))+')','') +  CASE WHEN DataType like '%PRIMARY KEY%' THEN '' WHEN DataType like '%FOREIGN KEY%' THEN '' ELSE ' NULL' END, ','+CHAR(13)) WITHIN GROUP (ORDER BY ColumnSequence) 
	+ CHAR(13)+')') as gen_sql
FROM
	(SELECT DISTINCT 
		j.TableName, 
		j.ColumnName,
		MAX(j.ColumnSequence) AS ColumnSequence, 
		j.DataType, 
		j.DataTypePrecision, 
		j.[level] 
	FROM 
		##parsedJson j
		CROSS APPLY (SELECT TOP 1 ParentTableName + 'Id' AS ColumnName FROM ##parsedJson p WHERE j.TableName = p.TableName ) p
	GROUP BY
		j.TableName, j.ColumnName,p.ColumnName, j.DataType, j.DataTypePrecision, j.[level] 
	) j
GROUP BY
	TableName