How to read JSON data with SQL Server?

Question

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

I have a column Textwith this Json code:

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

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

Answer 1

If you are looking for generic SQL Server JSON for tables and tables for JSON in SQL Server 2012, this article provides parseJSONa as well dbo.ToJSON. Using this code, you can run the following code:

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

this request 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

JSON, 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

: