Query JSON inside SQL Server 2012 column

Question

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. ``` Is there a way to query JSON data in SQL Server 2012?

Accepted Answer

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

And sadly, that's as easy as it can be.

Query JSON inside SQL Server 2012 column

Solution

Problem

Related problems