如何用SQL将无固定结构的JSON转换为关系型数据？

针对存储在SQL Server中的无固定嵌套结构JSON数据，可通过递归解析JSON层级+动态SQL实现自动化拆分到关系型表，以下是具体实现逻辑和代码示例：

一、核心思路 #

1. 递归解析JSON的所有层级、键名和数据类型，自动识别嵌套结构；
2. 根据解析结果动态生成对应关系型表（主表+子表），用自增主键关联嵌套层级；
3. 动态生成插入语句，将各层级JSON数据批量导入对应表中。

二、分步实现 #

1. 准备数据源表 #

先创建存储原始JSON的表并插入示例数据：

CREATE TABLE SourceTable (
    ID INT PRIMARY KEY,
    JSONData NVARCHAR(MAX)
);
INSERT INTO SourceTable VALUES (1, '{"A":"1","B":{"X":"AAA","Y":"BBB","C":{"AC":"1","BC":"2"}}}');

2. 递归解析JSON层级结构 #

用递归CTE提取所有JSON节点的路径、键、值和类型，以此识别嵌套对象：

WITH JSONHierarchy AS (
    -- 提取顶层JSON节点
    SELECT
        ID AS SourceID,
        CAST(N'$' AS NVARCHAR(MAX)) AS Path,
        [key] AS NodeKey,
        value AS NodeValue,
        type AS NodeType,
        1 AS Level
    FROM SourceTable
    CROSS APPLY OPENJSON(JSONData)
    UNION ALL
    -- 递归提取嵌套JSON对象的子节点
    SELECT
        jh.SourceID,
        CAST(jh.Path + N'.' + jh.NodeKey AS NVARCHAR(MAX)) AS Path,
        oj.[key],
        oj.value,
        oj.type,
        jh.Level + 1 AS Level
    FROM JSONHierarchy jh
    CROSS APPLY OPENJSON(jh.NodeValue) oj
    WHERE jh.NodeType = 5 -- Type=5表示当前节点是JSON对象（嵌套结构）
)
SELECT * FROM JSONHierarchy ORDER BY Level, Path;

3. 动态生成关系型表结构 #

根据解析出的层级，自动创建主表和子表，嵌套对象对应子表的主键关联主表的外键：

DECLARE @CreateTablesSQL NVARCHAR(MAX) = N'';

-- 生成主表（对应顶层JSON节点）
SELECT @CreateTablesSQL += N'
CREATE TABLE PrincipalTable (
    ID INT PRIMARY KEY,
    ' + STRING_AGG(
        CASE WHEN NodeType != 5 THEN QUOTENAME(NodeKey) + N' NVARCHAR(MAX)' 
             ELSE QUOTENAME(NodeKey) + N' INT' -- 嵌套对象字段作为外键，关联子表主键
        END, N',
    ') + N'
);'
FROM JSONHierarchy
WHERE Level = 1 AND SourceID = 1; -- 可扩展为合并所有数据源的结构，生成通用表

-- 生成子表（每个嵌套JSON对象对应一个子表）
SELECT @CreateTablesSQL += N'
CREATE TABLE ' + QUOTENAME('Table' + CAST(Level-1 AS NVARCHAR)) + N' (
    ' + QUOTENAME(NodeKey) + N' INT PRIMARY KEY,
    ' + STRING_AGG(
        CASE WHEN oj.NodeType !=5 THEN QUOTENAME(oj.NodeKey) + N' NVARCHAR(MAX)' 
             ELSE QUOTENAME(oj.NodeKey) + N' INT'
        END, N',
    ') + N'
);'
FROM JSONHierarchy jh
CROSS APPLY OPENJSON(jh.NodeValue) oj
WHERE jh.NodeType = 5 AND jh.SourceID = 1
GROUP BY jh.Level, jh.NodeKey;

-- 执行建表语句
EXEC sp_executesql @CreateTablesSQL;

4. 动态插入数据到各表 #

生成插入语句，将JSON各层级数据导入对应表，同时生成关联主键：

DECLARE @InsertDataSQL NVARCHAR(MAX) = N'';

-- 插入主表数据，嵌套对象字段用自增ID
SELECT @InsertDataSQL += N'
INSERT INTO PrincipalTable (ID, ' + STRING_AGG(QUOTENAME(NodeKey), N', ') + N')
SELECT 
    SourceID,
    ' + STRING_AGG(
        CASE WHEN NodeType !=5 THEN N'value' 
             ELSE N'ROW_NUMBER() OVER(ORDER BY SourceID)' -- 生成主表外键值
        END, N',
    ') + N'
FROM SourceTable
CROSS APPLY OPENJSON(JSONData);'
FROM JSONHierarchy
WHERE Level = 1 AND SourceID = 1;

-- 插入子表数据，关联父级ID
SELECT @InsertDataSQL += N'
INSERT INTO ' + QUOTENAME('Table' + CAST(jh.Level-1 AS NVARCHAR)) + N' (' + QUOTENAME(jh.NodeKey) + N', ' + STRING_AGG(QUOTENAME(oj.NodeKey), N', ') + N')
SELECT
    ROW_NUMBER() OVER(ORDER BY st.ID), -- 生成子表主键
    ' + STRING_AGG(
        CASE WHEN oj.NodeType !=5 THEN N'oj.value' 
             ELSE N'ROW_NUMBER() OVER(PARTITION BY st.ID ORDER BY oj.[key])'
        END, N',
    ') + N'
FROM SourceTable st
CROSS APPLY OPENJSON(st.JSONData, ''' + jh.Path + N''') oj
WHERE st.ID = ' + CAST(jh.SourceID AS NVARCHAR) + N';'
FROM JSONHierarchy jh
WHERE jh.NodeType = 5 AND jh.SourceID = 1;

-- 执行插入语句
EXEC sp_executesql @InsertDataSQL;

三、注意事项 #

• 结构兼容处理：如果不同调查问卷的JSON结构差异大，可先按问卷类型分组，为每组生成独立的表结构；
• 主键唯一性：建议用IDENTITY(1,1)或NEWID()生成主键，避免不同数据源的主键冲突；
• SQL注入防护：所有表名、字段名必须用QUOTENAME()包裹，避免动态SQL注入风险；
• 错误处理：可在动态SQL中添加TRY...CATCH块，捕获执行异常并记录日志。

内容的提问来源于stack exchange，提问作者George B