none
Reading JSON data stored in SQL Server

    Question

  • 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
    • Moved by Kalman Toth Friday, May 16, 2014 12:52 PM Not T-SQL
    Friday, May 16, 2014 9:23 AM

Answers

  • Sorry, I don't know any tool that allows you import JSON data to SQL Server.

    Do you already have the JSON data in SQL Server?

    If Yes, It isn't very difficult to write a CLR table valued funtion that returns the data in the following form

    FieldName        FieldValue
    ResposeCode      0
    TransactionId    null
    Amount           200.00
    message          Account Balance


    "No darás tropezón ni desatino que no te haga adelantar camino" Bernardo Balbuena


    Friday, May 16, 2014 1:45 PM
  • The problem is imho that a JSON is an object representation. So one table per class is required in a strict relational model. How many different classes do you have?

    Another approach would be using XML. Transforming JSON to XML is quite straight forward. A XML column could be indexed, thus you get improved query performance.

    Friday, May 16, 2014 1:50 PM

All replies

  • You need to tranform the JSON data into relational data. I would write a c# console application that uses JSON.net  to parse the JSON data and load it into SQL Server using SqlBulkCopy class.


    "No darás tropezón ni desatino que no te haga adelantar camino" Bernardo Balbuena

    Friday, May 16, 2014 10:57 AM
  • Thanks but I don't have great development skills, DBA with some T-SQL skills. Is there any tool available free or licensed that can do this. Thanks again for your valuable suggestion.

    Regards

    Chandan

    Friday, May 16, 2014 12:32 PM
  • Sorry, I don't know any tool that allows you import JSON data to SQL Server.

    Do you already have the JSON data in SQL Server?

    If Yes, It isn't very difficult to write a CLR table valued funtion that returns the data in the following form

    FieldName        FieldValue
    ResposeCode      0
    TransactionId    null
    Amount           200.00
    message          Account Balance


    "No darás tropezón ni desatino que no te haga adelantar camino" Bernardo Balbuena


    Friday, May 16, 2014 1:45 PM
  • The problem is imho that a JSON is an object representation. So one table per class is required in a strict relational model. How many different classes do you have?

    Another approach would be using XML. Transforming JSON to XML is quite straight forward. A XML column could be indexed, thus you get improved query performance.

    Friday, May 16, 2014 1:50 PM