I have table that stores the information about the columns that JSON schema file needs to hold like tag name, its SQL Server type etc.. see below.
I need to write SQL that will create JSON output. I was able to achieve this only when there is no nesting is available. But I am stuck because my json needs to be nested.
in below example, I can store nested node names like "Details:" and "Name:" in new column but not sure how to use them to look like this.
I did level 0 nesting with below query.
Select
STRING_AGG(CAST(CONCAT(REPLICATE(' ', 0), '"', friendlyname, '":{"type":["', commontype, '"', CASE WHEN nullable = 1 THEN ',"null"' ELSE '' END, '], "sqltype":"', sqltype,
CASE WHEN maxlength IS NULL AND precision IS NULL AND scale IS NULL THEN '' ELSE CONCAT('(', maxlength, CAST(precision AS VARCHAR(2)) + ',', scale, ')') END, '", "nullable":"',
CASE WHEN nullable = 1 THEN 'true' ELSE 'false' END, '", "Description":"', description, '"}') AS VARCHAR(MAX)),
CONCAT(',', CHAR(13), CHAR(10))) WITHIN GROUP (ORDER BY friendlyname)
FROM Mytable
Table Structure
| Column_name | Type | Length |
|---|---|---|
| dyecode | varchar | 20 |
| friendlyname | sysname | 256 |
| required | bit | 1 |
| commontype | varchar | 80 |
| sqltype | sysname | 256 |
| maxlength | smallint | 2 |
| precision | tinyint | 1 |
| scale | tinyint | 1 |
| nullable | bit | 1 |
| description | varchar | 128 |
Table Data
| friendlyname | required | commontype | sqltype | maxlength | precision | scale | nullable | description |
|---|---|---|---|---|---|---|---|---|
| AccountNo | 1 | number | int | NULL | NULL | NULL | 0 | |
| AccountRegisterDate | 1 | string | date | NULL | NULL | NULL | 0 | |
| AccountSum | 1 | number | decimal | NULL | 28 | 4 | 0 | |
| MarketValue | 1 | number | decimal | NULL | 28 | 4 | 0 | |
| FirstName | 1 | string | varchar | 100 | NULL | NULL | 0 | |
| LastName | 1 | string | varchar | 100 | NULL | NULL | 0 | |
| MiddleName | 1 | string | varchar | 100 | NULL | NULL | 0 |
Required Output:
{*-- nest level 0*
"AccountNo": {
"type": [
"number"
],
"sqltype": "int",
"nullable": "false",
"Description": ""
},
"AccountRegisterDate": {
"type": [
"string"
],
"sqltype": "date",
"nullable": "false",
"Description": ""
},
"AccountSum": {
"type": [
"number"
],
"sqltype": "decimal(28,4)",
"nullable": "false",
"Description": ""
},
"Details": { *-- nest level 1*
"Name": { *-- nest level 2*
"FirstName": {
"type": [
"string"
],
"sqltype": "varchar(100)",
"nullable": "false",
"Description": ""
},
"LastName": {
"type": [
"string"
],
"sqltype": "varchar(100)",
"nullable": "false",
"Description": ""
},
"MiddleName": {
"type": [
"string"
],
"sqltype": "varchar(100)",
"nullable": "false",
"Description": ""
}
}
}
}
