嘿，我来帮你搞定SQL Server 2016里把嵌套JSON转成关系格式的事儿！SQL Server 2016自带的OPENJSON函数刚好能完美处理这种嵌套结构的JSON数据，下面给你两种实用的实现方案：

假设你的表名为YourJsonTable，存储JSON内容的字段名为JsonColumn，你可以根据实际情况替换这两个名称。

方案一：逐层解析嵌套对象（适合复杂/可变结构） #

这种方式通过多次CROSS APPLY逐层拆解JSON的嵌套结构，灵活性更高，适合JSON字段可能有变动的场景：

SELECT 
    -- 提取AFP数组中的顶层字段
    j1.AGREEMENTID,
    -- 解析LoanAccounts嵌套对象的字段
    JSON_VALUE(j2.value, '$.Product') AS Product,
    JSON_VALUE(j2.value, '$.BUCKET') AS BUCKET,
    JSON_VALUE(j2.value, '$.ZONE') AS ZONE,
    JSON_VALUE(j2.value, '$.Region') AS Region,
    JSON_VALUE(j2.value, '$.STATE') AS STATE,
    JSON_VALUE(j2.value, '$.Year') AS Year,
    JSON_VALUE(j2.value, '$.Month') AS Month,
    JSON_VALUE(j2.value, '$.Day') AS Day,
    -- 解析FeedbackInfo嵌套对象的字段
    JSON_VALUE(j3.value, '$.FeedbackDate') AS FeedbackDate,
    JSON_VALUE(j3.value, '$.DispositionDate') AS DispositionDate,
    JSON_VALUE(j3.value, '$.DispositionCode') AS DispositionCode,
    -- 解析PaymentInfo嵌套对象的字段
    JSON_VALUE(j4.value, '$.ReceiptNo') AS ReceiptNo,
    JSON_VALUE(j4.value, '$.ReceiptDate') AS ReceiptDate
FROM 
    YourJsonTable
    -- 第一层：拆解最外层的AFP数组
    CROSS APPLY OPENJSON(JsonColumn, '$.AFP') 
        WITH (
            AGREEMENTID NVARCHAR(50) '$.AGREEMENTID',
            LoanAccounts NVARCHAR(MAX) '$.LoanAccounts' AS JSON, -- 标记为JSON类型，方便后续解析
            FeedbackInfo NVARCHAR(MAX) '$.FeedbackInfo' AS JSON,
            PaymentInfo NVARCHAR(MAX) '$.PaymentInfo' AS JSON
        ) AS j1
    -- 第二层：拆解LoanAccounts对象
    CROSS APPLY OPENJSON(j1.LoanAccounts) AS j2
    -- 第三层：拆解FeedbackInfo对象
    CROSS APPLY OPENJSON(j1.FeedbackInfo) AS j3
    -- 第四层：拆解PaymentInfo对象
    CROSS APPLY OPENJSON(j1.PaymentInfo) AS j4

方案二：直接嵌套定义字段（适合固定结构） #

如果你的JSON结构是固定不变的，这种写法更简洁高效，不需要多次CROSS APPLY：

SELECT 
    AGREEMENTID,
    Product,
    BUCKET,
    ZONE,
    Region,
    STATE,
    Year,
    Month,
    Day,
    FeedbackDate,
    DispositionDate,
    DispositionCode,
    ReceiptNo,
    ReceiptDate
FROM 
    YourJsonTable
    CROSS APPLY OPENJSON(JsonColumn, '$.AFP')
        WITH (
            AGREEMENTID NVARCHAR(50) '$.AGREEMENTID',
            -- 直接通过路径嵌套提取LoanAccounts的字段
            Product NVARCHAR(50) '$.LoanAccounts.Product',
            BUCKET INT '$.LoanAccounts.BUCKET',
            ZONE NVARCHAR(100) '$.LoanAccounts.ZONE',
            Region NVARCHAR(100) '$.LoanAccounts.Region',
            STATE NVARCHAR(50) '$.LoanAccounts.STATE',
            Year INT '$.LoanAccounts.Year',
            Month INT '$.LoanAccounts.Month',
            Day INT '$.LoanAccounts.Day',
            -- 嵌套提取FeedbackInfo的字段
            FeedbackDate DATETIME2 '$.FeedbackInfo.FeedbackDate',
            DispositionDate DATETIME2 '$.FeedbackInfo.DispositionDate',
            DispositionCode NVARCHAR(10) '$.FeedbackInfo.DispositionCode',
            -- 嵌套提取PaymentInfo的字段
            ReceiptNo NVARCHAR(50) '$.PaymentInfo.ReceiptNo',
            ReceiptDate DATETIME2 '$.PaymentInfo.ReceiptDate'
        )

关键说明 #

• OPENJSON：核心函数，负责将JSON数组/对象转换为关系型行集，'$.AFP'指定了要解析的JSON路径。
• JSON_VALUE：用于提取JSON中的单个标量值（字符串、数字、日期等），需要指定精确的字段路径。
• AS JSON：标记某个字段为JSON类型，让后续的OPENJSON可以继续解析这个嵌套对象。
• 字段类型匹配：要确保SQL中定义的字段类型和JSON中的数据类型一致，比如日期用DATETIME2兼容ISO格式，数字用INT/BIGINT等。

内容的提问来源于stack exchange，提问作者priyanka.sarkar