How to read JSON data from SQL Server?

Question

I want to read Json data from SQL Server 2012, I know that it will support read Json data in SQL Server 2016, but I need a workaround way to read it now.

I have a column Text with this Json code:

{"en": "Green", "ar": "أخضر"}

I want a procedure or function to give it language prefix and return to me the value.

Alex · Accepted Answer · 2018-04-13T09:55:19.593

If you are looking for a generic SQL Server JSON to Table and Table to JSON in SQL Server 2012, this article provides a parseJSON and a dbo.ToJSON function. Using this code, you can run the following code:

select * from dbo.parseJSON('  
    {
        "en": "Green",
        "ar": "أخضر"
    }
')

this query will return

element_id | sequenceNo | parent_Id | Object_ID | Name | StringValue | ValueType
-----------+------------+-----------+-----------+------+-------------+-----------
1          | 0          | 1         | NULL      | en   | Green       | string
2          | 0          | 1         | NULL      | ar   | أخضر        | string
3          | 1          | NULL      | 1         | -    |             | object

If one of your columns in a table is a JSON document, you can use a CROSS APPLY:

create table dbo.test(ID int identity(1,1) , json varchar(max))

insert into dbo.test (json)
select '  
    {
        "en": "Green",
        "es": "Verde"
    }
'

insert into dbo.test (json)
select '  
    {
        "en": "Red",
        "es": "Rojo"
    }


select * from dbo.test t
cross apply (select * from dbo.parseJSON(json)) details

This will return the following output:

thank you :) I don't want function that have Json string and return me table , I want something more simple . example function ( lang_prefix){ select [Text] from MyTable ..... here code to return for example 'Green' if lang_prefix is 'en'} — Duha, Nov 23 '15 at 09:46
If one of the columns of a table has a string value that is JSON . What do I do? — Kevin Burton, Apr 09 '18 at 16:40
@KevinBurton, I have updated my answer to show an example when a table has a string value that is JSON — Alex, Apr 13 '18 at 09:49

How to read JSON data from SQL Server?

1 Answers1