如何用SQL的JSON_QUERY函数将键为动态ID的JSON转换为表格？

嘿，这个问题我之前帮不少同行解决过——当JSON里的键是动态变化的ID时，确实没法用固定路径的JSON_QUERY直接处理，不过咱们可以靠SQL Server里的OPENJSON来搞定动态键的解析，下面给你具体的步骤和实用例子：

核心思路：把动态键拆成行再解析 #

OPENJSON的一大优势就是能把JSON对象直接拆解成键值对的行数据，不管你的键（也就是这里的动态ID）是什么内容，它都能自动识别并提取。

举个实际例子

假设你的JSON结构是这样的（动态ID作为顶级键，每个ID对应一段用户数据）：

{
  "1001": {"name": "Alice", "age": 30},
  "1002": {"name": "Bob", "age": 28},
  "1003": {"name": "Charlie", "age": 35}
}

第一步：提取动态ID和对应的子JSON

先通过OPENJSON把每个动态ID和它的子JSON拆成独立行：

DECLARE @json NVARCHAR(MAX) = N'{
  "1001": {"name": "Alice", "age": 30},
  "1002": {"name": "Bob", "age": 28},
  "1003": {"name": "Charlie", "age": 35}
}'

SELECT 
  [key] AS ID, -- 这里的[key]就是动态生成的ID值
  value AS UserDetails -- 对应的子JSON内容
FROM OPENJSON(@json)

执行后会得到这样的结果：

第二步：把子JSON解析成结构化列

接下来用CROSS APPLY结合OPENJSON，把每个子JSON也解析成具体的列：

DECLARE @json NVARCHAR(MAX) = N'{
  "1001": {"name": "Alice", "age": 30},
  "1002": {"name": "Bob", "age": 28},
  "1003": {"name": "Charlie", "age": 35}
}'

SELECT 
  main.[key] AS ID,
  sub.name,
  sub.age
FROM OPENJSON(@json) main
CROSS APPLY OPENJSON(main.value) WITH (
  name NVARCHAR(50) '$.name', -- 定义子JSON的字段和类型
  age INT '$.age'
) sub

这时候就得到了完全结构化的表格：

适配更复杂的嵌套JSON

如果你的JSON外层还有其他属性（比如元数据），动态ID在某个子节点里，比如：

{
  "metadata": {"sync_date": "2024-05-20"},
  "user_list": {
    "1001": {"name": "Alice", "age": 30},
    "1002": {"name": "Bob", "age": 28}
  }
}

只需要在OPENJSON里指定路径到包含动态ID的节点即可：

SELECT 
  main.[key] AS ID,
  sub.name,
  sub.age
FROM OPENJSON(@json, '$.user_list') main -- 定位到user_list节点
CROSS APPLY OPENJSON(main.value) WITH (
  name NVARCHAR(50) '$.name',
  age INT '$.age'
) sub

注意事项 #

• 这个方法适用于SQL Server 2016及以上版本，以及Azure SQL Database，因为OPENJSON是从SQL Server 2016开始引入的功能。
• 如果子JSON的结构也有动态变化，可以去掉WITH子句，让OPENJSON自动解析子节点的键值对，不过这种情况下结果会是键值对的行，需要再做处理。

总之，用OPENJSON+CROSS APPLY的组合，完全不需要依赖固定的ID路径，就能轻松把带动态键的JSON转换成结构化表格。

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