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
Select * FromparseJSON('[{"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:
- https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/
- http://msdn.microsoft.com/en-us/library/ms187928.aspx
- http://www.w3schools.com/sql/func_convert.asp
