在SQL Server 2012列中查询JSON

Question

我有一个 SQL Server 2012 表中的列，其中包含以下 Json 数据。

[{"bvin":"145a7170ec1247cfa077257e236fad69","id":"b06f6aa5ecd84be3aab27559daffc3a4"}]

现在我想在我的查询中使用这列数据，例如：

select * 
from tb1 
left join tb2 on tb1.(this bvin inside my column) = tb2.bvin.

在SQL Server 2012中是否有一种方法可以查询JSON数据？

- isumit

---

您是否具有管理员访问权限以对 SQL Server 进行更改？ - Andrey Borisko

不，我没有管理员访问权限。我认为我需要通过子字符串获取值。 - isumit

有些东西告诉我，Douglas Crockford听到将JSON存储在SQL表中的想法会感到不安...我会考虑重新设计这个。 - SqlACID

@isumit 考虑编写一个标量值函数来返回你正在寻找的 JSON 元素值。尽管如此，那是一个潜在的昂贵连接。 - bvj

可能是在TSQL中解析JSON的重复问题。 - bummi

Answer 1

老实说，这是一种非常糟糕的存储数据架构，可能会导致严重的性能问题。

如果你真的无法控制数据库更改，你可以通过像下面这样使用SUBSTRING解析出值，但这将引向非常不愉快的道路：

SELECT *
FROM tb1
JOIN tb2 on tb2.bvin = 
    SUBSTRING(
        tb1.json
        ,CHARINDEX('"bvin":"', tb1.json) + LEN('"bvin":"')
        ,CHARINDEX('"', tb1.json, CHARINDEX('"bvin":"', tb1.json) + LEN('"bvin":"')) - CHARINDEX('"bvin":"', tb1.json) - LEN('"bvin":"')
    )

很遗憾，这就是最简单的方法了。

- arserbin3

---

什么架构不适合存储数据？ - bvj

感谢 @arserbin3 !! 由于我无法在此重构或修改数据库，因此在此使用 SUBSTRING 解决了问题。 - isumit

将外键存储在 JSON 数据的 varchar 列中，需要在连接表之前对其进行解析，这极大地复杂化了查询，并且无法对其进行索引。而在 varchar 上连接表也比在 int 上慢得多。 - arserbin3

@arserbin3 我知道提问者在询问 Sql Server 2012，但我想指出 PostgreSQL 允许以高效的方式对 Json 进行索引和查询。 - marknuzz

JSON函数现在已经包含在Sql Server 2016中： https://msdn.microsoft.com/zh-cn/magazine/mt797647.aspx - boateng

在把JSON作为一列的情况下，其实没有什么可怕的。正如其他人所提到的那样，PostgreSQL很久以前就支持了这种做法。 - Wodzu

Answer 2

另一个解决方案是JSON Select，它提供了一个JsonNVarChar450()函数。你可以这样解决你的问题：

select * 
from tb1 
left join tb2 on dbo.JsonNVarChar450(tb1.YourColumnName, 'bvin') = tb2.bvin

正如有人提到的那样，这可能会有点慢，但是您可以使用JSON Select函数添加索引，如下所示：

alter table tb2 add
bvin as dbo.JsonNVarChar450(YourColumnName, 'bvin') persisted

go

create index IX_tb2_bvin on tb2(bvin)

从那时起，您可以使用计算列bvin上的索引进行查询，像这样：

select * 
from tb1 
left join tb2 on tb1.bvin = tb2.bvin

声明： 我是JSON Select的作者，因此我希望您使用它:)

（译者注：JSON Select是一种用于在JSON数据中选择和过滤元素的工具）

- joshuahealy

---

这个库看起来很有趣。有没有一个脚本可以运行来安装它？我不太喜欢 .exe 的方式... - Denis

嗨@denis，安装程序确保启用CLR集成，然后通过SQL语句执行安装程序。当然，可以通过脚本来完成，但是我目前没有预先制作的脚本可以提供给您。如果您担心它会做什么，您可以使用SQL Server Profiler查看在安装期间执行了哪些语句。 - joshuahealy

Answer 3

请在此处为该功能投票：这里。在解决方法部分，您可以找到基于函数的解决方案链接：http://www.sqlservercentral.com/articles/JSON/68128/和https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/ 在你的情况下，您需要合并该列中的所有值以创建一个数组，然后应用上述的解决功能来创建一个表。但是，我认为这不是一个解决方案，因为它会非常慢。也许您可以在插入时将这些值分别分成单独的列（插入存储过程或后端方法，可能是触发器..不确定您的访问权限）。

- Andrey Borisko

---

无需再进行投票。JSON将在Sql Server 2016 CTP3中提供。 - Jovan MSFT