JSON request inside SQL Server 2012 column

Question

I have a column inside my SQL Server 2012 table containing the following Json data.

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

Now I want to use the data of this column in my query, for example

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

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

Answer 1

Honestly, this is a terrible storage architecture and can lead to serious performance issues.

If you really don't have control to modify the database, you can accomplish this by parsing the value with SUBSTRINGas shown below, but this leads to a very unfortunate 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":"')
    )

And unfortunately, it is as easy as it can be.

Answer 2

JSON Select, JsonNVarChar450(). :

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

- , , JSON :

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, , :)

Answer 3

, . : http://www.sqlservercentral.com/articles/JSON/68128/ https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/

in your case, you need to combine all the values ​​from this column to create an array, and then apply the workaround functions described above to create the table. however, I DO NOT think this is a solution because it will be very slow. Perhaps you can split these values ​​into separate columns at the time of insertion (insert the stored proc or backend method, possibly a trigger .. not sure about your permissions)