6

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
isumit
  • 2,313
  • 4
  • 23
  • 28
  • Are you running this query in studio manager or an app. If it is an app then what programming language? – w0051977 May 18 '14 at 15:24
  • I am running this in SQL Server 2012. I need this in my stored procedure. – isumit May 18 '14 at 15:28
  • Do you have admin access to make changes to SQL Server? – Andrey Borisko May 18 '14 at 15:41
  • Nope i dont have the admin access. I thing i need to get values by substring – isumit May 18 '14 at 15:46
  • Something tells me Douglas Crockford would cringe at the idea of storing JSON in a SQL table...I would look at re-engineering this.. – SqlACID May 18 '14 at 15:52
  • @SqlACID are you suggesting the storing of unstructured column data is preferred? – bvj May 19 '14 at 04:52
  • 1
    @isumit consider writing a Scalar-valued function to return the JSON element value you're looking for. Nonetheless, that's a potentially expensive join. – bvj May 19 '14 at 04:57
  • @bvj Thanks, For now I used the substring for a quick solution and that works !! I will try Scalar function or some better option later when i get back to this if time allows – isumit May 19 '14 at 10:00
  • 1
    possible duplicate of [Parse JSON in TSQL](http://stackoverflow.com/questions/2867501/parse-json-in-tsql) – bummi Jun 25 '15 at 07:13

3 Answers3

7

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.

arserbin3
  • 6,010
  • 8
  • 36
  • 52
  • 1
    What architecture is terrible for storing the data? – bvj May 19 '14 at 04:50
  • Thanks @arserbin3 !! As i can't restructure or modify database here, so using SUBSTRING here solved the purpose. – isumit May 19 '14 at 09:57
  • 5
    @bvj: storing a foreign key in a `varchar` column of json data, where it has to be parsed before joining tables, vastly complicates queries, and removes the ability to index it. And joining tables on `varchar` is also much slower than an `int` – arserbin3 May 19 '14 at 11:26
  • 2
    @arserbin3 I know the question author was asking for Sql Server 2012, but just wanted to point out that PostgreSQL allows indexing and querying of Json in an efficient manner. – marknuzz Apr 22 '15 at 19:09
  • 1
    JSON functions are now included in Sql Server 2016: https://msdn.microsoft.com/en-us/magazine/mt797647.aspx – boateng Sep 13 '17 at 19:06
  • There is nothing horrible in having JSON as a column, as other mentioned PostgreSQL supports thats for a long time. – Wodzu Apr 10 '18 at 11:29
6

Another solution is JSON Select which providers a JsonNVarChar450() function. Your example would be solved like so:

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:

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:

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

joshuahealy
  • 3,529
  • 22
  • 29
  • This looks like an interesting library. Is there a script I can run to install it? I don't really like the .exe approach... – Denis Mar 15 '17 at 19:09
  • Hi @denis, the installer ensures that CLR integration is enabled and then installs the assembly via SQL statement execution. Of course it is possible to just do it via a script but I don't have a pre-made one that I can give you at this point. If you're worried about what it does you could use sql server profiler to see what statements get executed during an install. – joshuahealy Mar 17 '17 at 21:20
3

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/

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)

Andrey Borisko
  • 4,511
  • 2
  • 22
  • 31