SQL - Query to JSON

Question

I want to convert a result from a select into JSON format, to do this I found the following script

select rankName as name
    from tbUserHasRank 
    where userTag = 'test' 
    for json auto

however I receive the error

Incorrect syntax near 'json'.

so... what's incorrect with my syntax I wonder?

Without being sure I would say, `SQL Server 11.0.2100´ express version — Michael Tot Korsgaard, Jun 21 '16 at 15:50
Michael, this looks like its a feature in 2016: https://msdn.microsoft.com/en-us/library/dn921882.aspx — logixologist, Jun 21 '16 at 15:51
Yes according to their page it says: SQL Server (starting with 2016) — logixologist, Jun 21 '16 at 15:52
I did a google search and it shows its 2012: https://www.google.com/search?q=SQL+Server+11.0.2100&oq=SQL+Server+11.0.2100&aqs=chrome..69i57.1536j0j7&sourceid=chrome&ie=UTF-8 — logixologist, Jun 21 '16 at 15:53
ah, I see. Is there a way to accomplish the same result in `SQL Server 2012`? — Michael Tot Korsgaard, Jun 21 '16 at 15:54
Not easily. Perhaps using C# to shape the data into an object, then serialize to JSON would be easier than writing your own JSON serializer function for SQL data. — db_brad, Jun 21 '16 at 16:57

score 0 · Answer 1 · answered Jun 21 '16 at 15:59

0

You can make your own SQL query ie

select '"name":' + '"' + rankName + '"'
from tbUserHasRank 
where userTag = 'test'

answered Jun 21 '16 at 15:59

domenicr

352
3
14

1

I don't think the OP expects this kind of solution and you might need to escape special characters, – C.Champagne Jun 21 '16 at 16:05
@C.Champagne yes special characters need to be escaped and formatting needs to done. – domenicr Jun 21 '16 at 16:17

score 0 · Answer 2 · edited May 23 '17 at 12:08

0

I think you could create a CLR function in C# (or whatever) or you can have a look at this answer but I am not sure that special characters are correctly escaped.

edited May 23 '17 at 12:08

Community

1
1

answered Jun 21 '16 at 16:39

C.Champagne

5,381
2
23
35

C# wouldn't be a good solution as my limitations state that I can only use SQL – Michael Tot Korsgaard Jun 21 '16 at 16:41
@MichaelTotKorsgaard have you tried the solution given by the second link? It uses xml capabilities of SQL Server. – C.Champagne Jun 21 '16 at 22:22

SQL - Query to JSON

2 Answers2