如何从SQL Server数据库表生成嵌套JSON Schema文件

我有一张存储JSON Schema所需列信息（如标签名、SQL Server类型等）的表，需要编写SQL语句生成JSON输出。目前仅能实现无嵌套的JSON，但生成嵌套JSON时遇到瓶颈。我可以在新列中存储如"Details:"、"Name:"这类嵌套节点名称，但不知如何利用它们生成目标结构。

现有0级嵌套查询语句 #

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

表结构 #

表数据 #

期望输出 #

{
  "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": {
    "Name": {
      "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": ""
      }
    }
  }
}

内容的提问来源于stack exchange，提问作者Paresh Maru