时间戳

首页 > 解决方案 > 选择存储在 SQL 中的动态 json 作为选择查询的一部分

sql - 选择存储在 SQL 中的动态 json 作为选择查询的一部分

问题描述

我知道类似的问题已经被问过多次，但是我的情况似乎有点不同。

我的数据库表是这样的：

App ID    |    ID     |     JSONData      |     URL      |     CreatedOn
----------+-----------+-------------------+--------------+-----------------
5b5cd8    |    1      | {"F":"B", "S":"D"}| http://local | Mar 19 2018 13:04
5b5cd8    |    2      | {"F":"C", "S":"K"}| http://remote| Mar 29 2018 09:34
6b9df0    |    3      | {"T":"N", "D":"S"}| http://site  | Apr 04 2018 16:12

App IDcolumn 可以有不同的值，但是相同的结构JSONData是（*应该是）相同的App ID。

无论如何我可以拆分JSONData数据并得到这样的结果吗？

App ID    |    ID     |  F  |  S  |     URL      |     CreatedOn
----------+-----------+-----+-----+--------------+-------------------------
5b5cd8    |    1      |  B  |  D  | http://local | Mar 19 2018 13:04
5b5cd8    |    2      |  C  |  K  | http://remote| Mar 29 2018 09:34

接下来App ID是这样的

App ID    |    ID     |  T  |  D  |     URL      |     CreatedOn
----------+-----------+-----+-----+--------------+-------------------------
6b9df0    |    3      |  N  |  S  | http://site  | Apr 04 2018 16:12

注意：字段中的数据JSONData大多是一层深度，即所有数据都是字符串，没有其他对象。

我发现的大多数时候都是这样的解决方案，要么使用静态 JSON 键名进行拆分，要么创建会导致性能问题的临时表。

标签： sqljsonsql-servertsql

解决方案

您已经被告知，结果集的列名必须事先知道。

唯一的解决方法是动态 SQL（将语句创建为字符串并EXEC()获取其结果）。但这有一些主要缺点（和一些优点）......

您可能会这样做（需要 SQL-Server 2016+）：

样机表

DECLARE @tbl TABLE(AppID VARCHAR(100),ID INT,JSONData NVARCHAR(MAX));
INSERT INTO @tbl VALUES
 ('5b5cd8',1,N'{"F":"B", "S":"D"}')
,('5b5cd8',2,N'{"F":"C", "S":"K"}')
,('6b9df0',3,N'{"T":"N", "D":"S"}');

--此查询使用JSON_VALUE
--您需要为每个可能的列列表创建一个语句 --
应用 aWHERE来过滤适当的行

SELECT t.AppID
      ,t.ID
      ,JSON_VALUE(t.JSONData,'$.F') AS F
      ,JSON_VALUE(t.JSONData,'$.S') AS S
FROM @tbl t
WHERE t.AppID='5b5cd8'

--您可能包括所有可能的列
--这 在没有过滤器的情况下有效，但会返回很多 NULL

SELECT t.AppID
      ,t.ID
      ,JSON_VALUE(t.JSONData,'$.F') AS F
      ,JSON_VALUE(t.JSONData,'$.S') AS S
      ,JSON_VALUE(t.JSONData,'$.T') AS T
      ,JSON_VALUE(t.JSONData,'$.D') AS D
FROM @tbl t

-- 更清晰/更好阅读OPENJSON()与-WITH子句有关

SELECT t.AppID
      ,t.ID
      ,JsonColumns.*
FROM @tbl t
CROSS APPLY OPENJSON(t.JSONData) WITH(F CHAR(1)
                                     ,S CHAR(1)
                                     ,T CHAR(1)
                                     ,D CHAR(1)) JsonColumns

我的建议：将最后一个创建为 VIEW 或（可能更好）一个 iTVF，并针对此使用专用语句，每种类型的结构一个。

推荐阅读