Parse JSON String by SQL script

The first question: Why do you need to parse JSON by SQL script? You can do it easily by C# or VB, or any PL.
But I sure that if you search this keyword and reach this post, you already have your own reason.
In my case, I got a task from Chris (my manager) in Microsoft, he want to migrate database, one of those step is parse about 100 k Json string and Insert into new table.
I have tried with C#, it worked, but I don't know how long, because I recognize that it is not a smart way and stop them ( program is running and thinking about C# solution). I try with SQL script.

Thanks Phil Factor for his articles about this problem

• First, you should create Function to Parst JSON, the scrip can be downloaded here: ParseJSON-FUNCTION.sql

And then try with script

Select * From

parseJSON

(

       '[{"Text":"YES","Value":"YES","Default":true},

       {"Text":"NO","Value":"NO","Default":false}]'

)

Great because we can get all field we need, but that is not the format we want. We should have table with three field TEXT, VALUE and Defaule.

That's why we need do more a little

• Use this script to query table from JSON string

Select

       max(case when name='Text' then convert(Varchar(50),StringValue) else '' end) as [Text],

       max(case when name='Value' then convert(Varchar(50),StringValue) else '' end) as [Value],

       max(case when name='Default' then convert(bit,StringValue) else 0 end) as [Default]

From parseJSON

(

       '[{"Text":"YES","Value":"YES","Default":true},

       {"Text":"NO","Value":"NO","Default":false}]'

)

where ValueType = 'string' OR ValueType = 'boolean'

group by parent_ID

And this is result

All things I need are some where here, I hope you too.

If you have some problem with this Function, feel free to comment here.

Thanks

Reference: 

1. https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/
2. http://msdn.microsoft.com/en-us/library/ms187928.aspx
3. http://www.w3schools.com/sql/func_convert.asp