时间戳

首页 > 解决方案 > 将json中的键值对数组转换为SQL Server中的表

json - 将json中的键值对数组转换为SQL Server中的表

问题描述

我有一个表，其中一列是某些已执行代码的 id，另一列是键值对数组，其中包含传递的参数名称和像这样格式化的值

executionId|params                                                           |

200001     |[{"key":"name","value":"john"},{"key":"surname","value":"smith"}]|

其中传递的参数数量是任意的，我想将其输出为表格格式。

我试过这个查询

SELECT 
   t.executionId,
   j.*
FROM #tmpTransac t
CROSS APPLY (
   SELECT * 
   FROM OPENJSON(t.[params])
   ) j

但我得到的输出是这个

executionId|key|value
1992013     0   {"key":"name","value":"john"}
1992013     1   {"key":"surname","value":"smith"}

我期待像

executionId| name |surname
1992013    | john |smith

标签： jsonsql-servertsql

解决方案

我的建议使用OPENJSON包括一个WITH子句和PIVOT

--样机表

DECLARE @tbl TABLE(executionId INT,params NVARCHAR(MAX))
INSERT INTO @tbl VALUES(200001,'[{"key":"name","value":"john"},{"key":"surname","value":"smith"}]');

- 查询

SELECT p.*
FROM
(
    SELECT t.executionId
          ,A.[key] AS ParamKey
          ,A.[value] AS ParamValue
    FROM @tbl t
    CROSS APPLY OPENJSON(t.params) WITH([key] NVARCHAR(MAX),[value] NVARCHAR(MAX)) A
) t
PIVOT(MAX(ParamValue) FOR ParamKey IN([Name],Surname)) p;

简而言之：

有点令人困惑的是，JSON 元素的名称与OPENJSON. 但是该WITH子句将在 JSON 中查找这些名称，因此这将返回一个类似的列表

name       john
surname    smith

要并排获得此信息，您可以使用任一聚合PIVOT或条件聚合-

推荐阅读