在Microsoft SQL Server中查询动态JSON键值存储并实现动态透视

核心思路 #

要实现键名不固定的动态透视，需分两步处理：

1. 将每个JSON数组拆分为带位置索引的行数据，确保不同键下的同位置元素能对齐；
2. 动态提取所有键名，构造透视SQL语句并执行。

具体实现代码 #

第一步：拆分JSON数组并生成索引

通过OPENJSON解析每个数组，利用其返回的key字段（对应数组索引）标记元素位置，让同位置的元素能匹配：

SELECT 
    L1.[KEY] AS ColumnName,
    CAST(L2.[KEY] AS INT) AS ElementIndex,
    CAST(L2.[Value] AS INT) AS ElementValue
FROM 
    openjson((SELECT TOP 1 KeyValueIntegers FROM [MatrixAnalyzer].[CountsMatrixInitial] WHERE MatrixId = 16), '$') AS L1
CROSS APPLY 
    openjson(L1.[Value], '$') AS L2

执行后会得到结构化的中间结果：

第二步：动态构造并执行透视SQL

由于键名不固定，先提取所有键名拼接成透视列，再生成完整的透视语句执行：

DECLARE @Columns NVARCHAR(MAX), @SQL NVARCHAR(MAX)

-- 提取所有需要作为透视列的键名
SELECT @Columns = STRING_AGG(QUOTENAME(ColumnName), ', ')
FROM (
    SELECT DISTINCT L1.[KEY] AS ColumnName
    FROM openjson((SELECT TOP 1 KeyValueIntegers FROM [MatrixAnalyzer].[CountsMatrixInitial] WHERE MatrixId = 16), '$') AS L1
) AS Cols

-- 构造动态透视SQL语句
SET @SQL = N'
SELECT ' + @Columns + '
FROM (
    SELECT 
        L1.[KEY] AS ColumnName,
        CAST(L2.[KEY] AS INT) AS ElementIndex,
        CAST(L2.[Value] AS INT) AS ElementValue
    FROM 
        openjson((SELECT TOP 1 KeyValueIntegers FROM [MatrixAnalyzer].[CountsMatrixInitial] WHERE MatrixId = 16), '$') AS L1
    CROSS APPLY 
        openjson(L1.[Value], '$') AS L2
) AS SourceData
PIVOT (
    MAX(ElementValue)
    FOR ColumnName IN (' + @Columns + ')
) AS PivotTable
ORDER BY ElementIndex
'

-- 执行动态SQL
EXEC sp_executesql @SQL

效率说明 #

• 用OPENJSON解析JSON是SQL Server原生高效的处理方式，避免自定义函数的额外开销；
• STRING_AGG（SQL Server 2017及以上版本支持）拼接列名，比传统FOR XML PATH更简洁高效；
• 基于结构化中间数据的透视逻辑清晰，适配任意数量的动态键名。

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