在 SQL Server 2012 列中查询 JSON
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23723473/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query JSON inside SQL Server 2012 column
提问by isumit
I have a column inside my SQL Server 2012 table which contains following Json data.
我的 SQL Server 2012 表中有一个列，其中包含以下 Json 数据。
[{"bvin":"145a7170ec1247cfa077257e236fad69","id":"b06f6aa5ecd84be3aab27559daffc3a4"}]
Now I want to use this column data in my query like
现在我想在我的查询中使用这个列数据
select *
from tb1
left join tb2 on tb1.(this bvin inside my column) = tb2.bvin.
Is there a way to query JSON data in SQL Server 2012?
有没有办法在 SQL Server 2012 中查询 JSON 数据？
回答by joshuahealy
Another solution is JSON Selectwhich providers a JsonNVarChar450()function. Your example would be solved like so:
另一个解决方案是JSON 选择哪些提供者一个JsonNVarChar450()函数。您的示例将像这样解决：
select *
from tb1
left join tb2 on dbo.JsonNVarChar450(tb1.YourColumnName, 'bvin') = tb2.bvin
as someone mentioned, this could be a bit slow, however you could add an index using the JSON Select function like so:
正如有人提到的，这可能有点慢，但是您可以使用 JSON Select 函数添加索引，如下所示：
alter table tb2 add
bvin as dbo.JsonNVarChar450(YourColumnName, 'bvin') persisted
go
create index IX_tb2_bvin on tb2(bvin)
And from then on you can query using the index over the computed column bvin, like so:
从那时起，您可以使用计算列 bvin 上的索引进行查询，如下所示：
select *
from tb1
left join tb2 on tb1.bvin = tb2.bvin
DISCLOSURE: I am the author of JSON Select, and as such have an interest in you using it :)
披露：我是 JSON Select 的作者，因此对您使用它有兴趣:)
回答by arserbin3
Honestly, this is a terrible architecture for storing the data, and can result in some serious performance issues.
老实说，这是一种糟糕的数据存储架构，可能会导致一些严重的性能问题。
If you truly don't have control to change the database, you canaccomplish this by parsing out the value with SUBSTRINGlike below, but it's leading down a very unhappy path:
如果您确实无法控制更改数据库，则可以通过解析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":"')
)
And sadly, that's as easy as it can be.
可悲的是，这很容易。
回答by Andrey Borisko
Please vote for the feature here. In workaround section there you can find links to function-based solutions: http://www.sqlservercentral.com/articles/JSON/68128/and https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/
请在此处为该功能投票。在解决方法部分，您可以找到基于函数的解决方案的链接：http: //www.sqlservercentral.com/articles/JSON/68128/和https://www.simple-talk.com/sql/t-sql-programming /consume-json-strings-in-sql-server/
in your case you need to merge all values from this column to create an array and then apply the workaround functionality mentioned above to create a table. however, I do NOT think this is a solution as it will be very slow. Maybe you could separate those values into separate columns in time of inserting(insert stored proc or backend method, maybe trigger.. not sure about your access rights)
在您的情况下，您需要合并此列中的所有值以创建一个数组，然后应用上述解决方法功能来创建一个表。但是，我不认为这是一个解决方案，因为它会很慢。也许您可以在插入时将这些值分成单独的列（插入存储的 proc 或后端方法，可能是触发器……不确定您的访问权限）
