如何用SQL解析无表头嵌套JSON并导入SQL数据库

针对你这种多层嵌套的无表头数组型JSON，需要逐层拆解数组层级，通过多次CROSS APPLY OPENJSON来提取每一层的数据，而非试图在单次OPENJSON里直接引用深层索引。以下是适配你数据结构的完整SQL方案：

数据结构拆解 #

先明确你的JSON层级逻辑：

1. 最外层：单元素数组，内部是类别级数组：[123, 1123, "94.05", [子类别数组]]
2. 子类别级：单元素数组，内部是子类别数组：[11234, 31040, "100.00", [明细数组]]
3. 明细级：多元素数组，每个元素是最终记录数组：[ID1, ID2, 分数, 结果, 状态]

完整SQL查询 #

SELECT 
    m.[udAuditID], 
    m.[assessmentID],
    -- 第一层（类别级）字段
    category.CatID,
    category.CategoryID,
    category.CategoryScore,
    -- 第二层（子类别级）字段
    sub_category.SubCatID,
    sub_category.SubCategoryID,
    sub_category.SubCategoryScore,
    -- 第三层（明细级）字段
    detail.DetailID,
    detail.DetailRefID,
    detail.DetailScore,
    detail.Result,
    detail.Status
FROM 
    [dbo].auditfinal m
-- 第一层：拆解最外层数组，提取类别级数据和子类别嵌套JSON
CROSS APPLY 
    OPENJSON(m.category_json)
    WITH (
        CatID BIGINT '$[0]',
        CategoryID BIGINT '$[1]',
        CategoryScore VARCHAR(50) '$[2]',
        SubCategoryJSON JSON '$[3]' -- 将子类别数组转为JSON类型，供下一层拆解
    ) AS category
-- 第二层：拆解子类别数组，提取子类别数据和明细嵌套JSON
CROSS APPLY 
    OPENJSON(category.SubCategoryJSON)
    WITH (
        SubCatID BIGINT '$[0]',
        SubCategoryID BIGINT '$[1]',
        SubCategoryScore VARCHAR(50) '$[2]',
        DetailJSON JSON '$[3]' -- 将明细数组转为JSON类型，供下一层拆解
    ) AS sub_category
-- 第三层：拆解明细数组，提取最终记录
CROSS APPLY 
    OPENJSON(sub_category.DetailJSON)
    WITH (
        DetailID BIGINT '$[0]',
        DetailRefID BIGINT '$[1]',
        DetailScore INT '$[2]',
        Result VARCHAR(10) '$[3]',
        Status VARCHAR(10) '$[4]'
    ) AS detail

关键说明 #

• 每次CROSS APPLY OPENJSON对应一个数组层级，通过WITH子句里的$[索引]定位无表头数组的元素
• 把下一层的嵌套数组用JSON类型字段保留（比如SubCategoryJSON），传递到下一层继续拆解
• 数据类型可根据实际需求调整（比如CategoryScore可转为DECIMAL(5,2)，DetailScore用INT或DECIMAL）

扩展建议 #

如果某一层级的数组可能包含多个元素（比如多个子类别），该方案依然有效，会自动展开所有子元素；若需过滤某一层的数据，可在对应的CROSS APPLY后添加WHERE条件。

内容的提问来源于stack exchange，提问作者Lakshmi Hariharan