在SQL Server 2012列中查询JSON [英] Query JSON inside SQL Server 2012 column

问题描述

我在SQL Server 2012表中有一列，其中包含以下Json数据.

I have a column inside my SQL Server 2012 table which contains following Json data.

[{"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.

是否可以在SQL Server 2012中查询JSON数据?

Is there a way to query JSON data in SQL Server 2012?

推荐答案

老实说，这是用于存储数据的糟糕架构，可能会导致一些严重的性能问题.

Honestly, this is a terrible architecture for storing the data, and can result in some serious performance issues.

如果您确实没有控制权来更改数据库，则可以 通过使用SUBSTRING解析值来完成此操作，如下所示，但这会导致一条非常不愉快的路:

If you truly don't have control to change the database, you can accomplish this by parsing out the value with SUBSTRING like below, but it's leading down a very unhappy path:

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":"')
    )

可悲的是，这很容易.

这篇关于在SQL Server 2012列中查询JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！