May 16, 2014 at 12:35 am
Hi Folks,
We have a gaming application which generates transactional data in MongoDB which eventually sends the data to SQL Server and it is in JSON format. This data needs to be used for reporting tool but visualizing this data in forms of a table is proving to be difficult. One example of a column we receive is:
{responseCode:0 transactionId:null amount:200.00 message:account balance }
We need to build a sort of ETL or batch job but need to interpret this in a form which SQL Server can understand.
Please share you views if you have have done something similar.
Thanks
Chandan Jha
May 16, 2014 at 1:03 am
I know Power Query can handle JSON pretty easily.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 16, 2014 at 2:06 am
Koen Verbeeck (5/16/2014)
I know Power Query can handle JSON pretty easily.
Thanks for your opinion. Before I google it, is it a tool which can interpret this and gives an option to store the data into SQL Server easily?
Regards
chandan
May 16, 2014 at 2:15 am
It is an Excel add-in and is part of the Power BI offering of Microsoft.
That means the data is read into Excel, which you'd have to read in with SSIS.
You can refresh the Excel workbook through SSIS:
Refresh an Excel Workbook with a Script Task[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 16, 2014 at 2:55 am
I googled over this and found that PowerView can consume this data but what about the data which is already stored in the form of characters and this needs to be visualized in form of different attributes. I am facning a difficulty here. the app is running fine on MongoDB but as such it is not suited for reporting so the data in JSON form is relayed to SQL Server for reporting purposes.
May 16, 2014 at 3:14 am
chandan_jha18 (5/16/2014)
I googled over this and found that PowerView can consume this data but what about the data which is already stored in the form of characters and this needs to be visualized in form of different attributes. I am facning a difficulty here. the app is running fine on MongoDB but as such it is not suited for reporting so the data in JSON form is relayed to SQL Server for reporting purposes.
You can also write C# code to parse JSON (there will be code examples on the net, I'm sure) or you can use TSQL to parse it directly into SQL Server.
Phil Factor already did the heavy lifting:
Consuming JSON Strings in SQL Server[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 23, 2014 at 2:04 pm
We convert between XML and JSON very regularly by using the JSON.NET library in c#.
you could write an exe that takes in the JSON and easily converts it to XML, and run that with a script task.
June 13, 2014 at 4:20 am
Hi,
If you want to do this in SSIS you'll need to write a script component to act as a datasource.
There are a few good resources out there that show you how to do it, here's one:
http://dennysjymbo.blogspot.co.uk/2013/05/using-json-feed-as-data-source-in-ssis.html
Cheers,
Jim.
June 13, 2014 at 6:45 am
Looking at the OP, if all you want to do is split the JSON string into key/value pairs for reporting, you can do thing with a string splitter. The following code will do your splitting by making use of Jeff Moden's DelimitedSplit8K function posted at http://www.sqlservercentral.com/articles/Tally+Table/72993/. If you aren't familiar with it yet, take the time to get acquainted. It's well worth the read and will change the way you look at data.
with json(string) as (
select 'responseCode:0 transactionId:null amount:200.00 message:account balance'),
value_pairs as (
select itemnumber, item
from json
cross apply DelimitedSplit8K(json.string, ' ')
where len(item) > 0)
select value_pairs.item original_item,
column_a = max(case when s.itemnumber = 1 then s.Item end),
column_b = max(case when s.itemnumber = 2 then s.Item end)
from value_pairs
cross apply DelimitedSplit8K(value_pairs.Item, ':') s
group by value_pairs.Item;
The string is split first by space, then by colon. You know ahead of time that you only have to allow for two values (name and value) in each split row.
June 14, 2014 at 2:42 am
I would say simply create a deserializer class in .Net and use it as a CLR obect to query the data through SQL Server.Of course this would come at expense of your server performance.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
December 25, 2015 at 4:38 am
Hello,
I am going to share a relevant link where you will get the answer that how to interpr JSON data in SQL server.
All steps is explained in relevant manner . For detail about task follow here and ig you get any problem , there is one section of help, just go there.
http://zappysys.com/products/ssis-powerpack/ssis-json-file-source
Hope it will be helpful.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply