SQL Server中解析JSON查询结果并转成行/列格式的方法

看起来你搞反了方向——FOR JSON Path是用来把行/列数据转换成JSON的，而你需要的是把存储在列里的JSON字符串解析成标准的行/列结果集，这时候应该用SQL Server的OPENJSON函数。

核心解决方案 #

假设你的表dbo.JsonData中存储JSON的列名为Json_Data（从你给出的示例内容推断），可以用CROSS APPLY配合OPENJSON来逐行解析JSON，并将其映射到对应的列上：

SELECT parsed_data.*
FROM dbo.JsonData
-- 逐行解析Json_Data列中的JSON字符串
CROSS APPLY OPENJSON(Json_Data)
WITH (
    -- 这里根据你的JSON结构定义列名、类型和JSON路径
    Serial_Number VARCHAR(20) '$.Serial_Number',
    Gateway VARCHAR(50) '$.Gateway',
    Device_Type VARCHAR(30) '$.Device_Type',
    -- 继续添加你需要提取的其他属性，格式：列名 类型 '$.JSON属性名'
) AS parsed_data

举个实际例子 #

如果你的表数据是这样的：

执行上面的查询后，会得到结构化的结果：

额外实用技巧 #

• 过滤无效JSON：如果表中存在格式错误的JSON，可以用ISJSON函数先过滤掉这些行，避免解析报错：

SELECT parsed_data.*
FROM dbo.JsonData
WHERE ISJSON(Json_Data) = 1 -- 只处理有效的JSON
CROSS APPLY OPENJSON(Json_Data)
WITH (
    Serial_Number VARCHAR(20) '$.Serial_Number',
    Gateway VARCHAR(50) '$.Gateway'
) AS parsed_data

• 处理嵌套JSON：如果你的JSON有嵌套结构（比如{"Serial_Number":"123","Meta":{"Location":"Room1","Floor":2}}），可以通过JSON路径直接提取嵌套属性：

WITH (
    Serial_Number VARCHAR(20) '$.Serial_Number',
    Location VARCHAR(50) '$.Meta.Location',
    Floor INT '$.Meta.Floor'
)

• 动态解析未知结构：如果你的JSON属性不固定，不想手动定义列，可以直接用不带WITH的OPENJSON，不过这会返回键值对形式的结果（每行对应一个属性）：

SELECT 
    jd.Json_Data,
    parsed.key AS PropertyName,
    parsed.value AS PropertyValue
FROM dbo.JsonData jd
CROSS APPLY OPENJSON(jd.Json_Data) parsed

为什么之前的FOR JSON Path不对？ #

FOR JSON Path的作用是把查询出来的行/列数据打包成一个JSON数组，所以它会把你表中所有行的内容合并成一个单行的JSON结果，这和你想要的"把JSON转成行/列"正好相反。

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