How to read JSON data from SQL Server? [duplicate]

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.

Answer 1

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: