动态SQL读取JSON生成INSERT语句失败问题求助

给定的JSON数据 #

DECLARE @json NVARCHAR(MAX) = N'{
    "results": [
        {
            "tables": [
                {
                    "rows": [
                        {
                            "[department_id]": 1111,
                            "[company_id]": 12345,
                            "[sum]": 38204410879
                        }
                    ]
                }
            ]
        }
    ]
}';

目标表结构 #

results(id, date, key_1, key_2, key_3, key_4, key_5, value_1, value_2, value_3, value_4, value_5)

注：原表结构中重复的key_4已修正为key_5

需求规则 #

• JSON中最后一个值固定为sum，需存入value_1列
• 其余值按JSON中的顺序依次存入key_1、key_2等列
• 需动态适配JSON结构的变化（键的数量可能改变）

现有问题 #

尝试通过循环提取JSON值时返回空结果，现有代码如下：

DECLARE @i INT = 0;
DECLARE @total_count INT = 3;
DECLARE @key NVARCHAR(MAX);

-- Loop through the JSON object to extract keys and values
WHILE @i < @total_count
BEGIN
    SET @key = CONCAT(', (SELECT [value] FROM OPENJSON(@json, ''$.results[0].tables[0].rows[0]'') WITH (keyIndex INT ''$.&quot;$id&quot; '') AS jsonKeys
                        CROSS APPLY OPENJSON(@json, ''$.results[0].tables[0].rows[0]'')
                        WHERE jsonKeys.keyIndex = ', @i, ')');
    PRINT @key;
    SET @i = @i + 1;
END

第一次循环打印的查询语句返回空：

SELECT [value]
FROM OPENJSON(@json, '$.results[0].tables[0].rows[0]') WITH (keyIndex INT '$."$id" ') AS jsonKeys
CROSS APPLY OPENJSON(@json, '$.results[0].tables[0].rows[0]')
WHERE jsonKeys.keyIndex = 0

预期生成的INSERT语句 #

INSERT INTO results(date, key_1, key_2, value_1) VALUES(GETDATE(), 1111, 12345, 38204410879)

解决方案 #

问题根源在于原代码中使用的$."$id"并不存在于JSON结构中，导致无法获取键值对的顺序。以下是正确的动态实现方案：

DECLARE @json NVARCHAR(MAX) = N'{
    "results": [
        {
            "tables": [
                {
                    "rows": [
                        {
                            "[department_id]": 1111,
                            "[company_id]": 12345,
                            "[sum]": 38204410879
                        }
                    ]
                }
            ]
        }
    ]
}';

-- 提取JSON行中的所有键值对，并生成顺序索引
DECLARE @keyValues TABLE (idx INT IDENTITY(0,1), [key] NVARCHAR(100), [value] NVARCHAR(MAX));
INSERT INTO @keyValues([key], [value])
SELECT [key], [value]
FROM OPENJSON(@json, '$.results[0].tables[0].rows[0]');

-- 获取键值对总数量及key列的数量
DECLARE @total INT = (SELECT COUNT(*) FROM @keyValues);
DECLARE @keyCount INT = @total - 1;

-- 动态拼接INSERT语句的列名和值部分
DECLARE @columns NVARCHAR(MAX) = 'date';
DECLARE @values NVARCHAR(MAX) = 'GETDATE()';

-- 添加key列对应的内容
DECLARE @i INT = 1;
WHILE @i <= @keyCount
BEGIN
    SET @columns += CONCAT(', key_', @i);
    SET @values += CONCAT(', ', (SELECT [value] FROM @keyValues WHERE idx = @i - 1));
    SET @i += 1;
END

-- 添加sum对应的value_1列
SET @columns += ', value_1';
SET @values += CONCAT(', ', (SELECT [value] FROM @keyValues WHERE idx = @total - 1));

-- 生成最终INSERT语句
DECLARE @insertSql NVARCHAR(MAX) = CONCAT('INSERT INTO results(', @columns, ') VALUES(', @values, ')');

-- 打印或执行语句
PRINT @insertSql;
-- EXEC sp_executesql @insertSql;

方案说明 #

1. 利用IDENTITY列生成键值对的顺序索引，确保与JSON中的原始顺序一致
2. 将所有键值对存入临时表，便于后续按顺序提取
3. 动态拼接INSERT语句的列名和值部分，自动适配JSON中键的数量变化
4. 最终生成的SQL完全符合需求，且无需依赖JSON中不存在的属性

内容的提问来源于stack exchange，提问作者Arun Augustine