json into sql server

  • Hi all,

    Not sure if the is the right forum for my question.

    I am trying to pull data from ElasticSearch into sql server 2K8R2.

    Data comes out in json format.

    Could someone suggest an approach or share a link for me to read more about it, please.

    Thanks,

  • I searched for "JSON" + "insert" here and found a ton of articles. Did you read any?

  • This article is quite thorough.

    Bonus: It's from Phil Factor

    https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/

  • Thanks for reply. I just found this same article.

    Problem is: I am going to have millions of records in json format and wonder if it would work because of what the author said:

    "I certainly wouldn't want to shred Facebook info in TSQL! I wrote this code for a particular purpose that used reasonably short documents"

  • Depending on what's in between ElasticSearch and SQL, a rudimentary .Net application can quite easily de-serialize the JSON before putting it into SQL.

    Executive Junior Cowboy Developer, Esq.[/url]

  • I have a client sending me .json files to feed into SQL for processing. I ended up hacking up a powershell script to read the json file to a powershell object, then I could iterate through the powershell object to pull out the pieces that I needed and stuff them into some staging tables.

    Look up cmdlet ConvertFrom-Json

  • Well with SQL Server 2016 you can import a JSON string into a SQL table. JSON in SQL will just work like XML with similar semantics.

    This feature is coming in CTP3, right now with CTP2 you can just export from SQL table to JSON string.

    Check here: https://msdn.microsoft.com/en-us/library/dn921882(v=sql.130).aspx

  • manub22 (6/4/2015)


    Well with SQL Server 2016 you can import a JSON string into a SQL table. JSON in SQL will just work like XML with similar semantics.

    This feature is coming in CTP3, right now with CTP2 you can just export from SQL table to JSON string.

    Check here: https://msdn.microsoft.com/en-us/library/dn921882(v=sql.130).aspx

    That's nice but do you have anything that will work with a currently released version?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Yes Jeff,

    The currently released version of SQL Server 2016 is CPT2, where you can only export SQL table rows in JSON string format.

    I've put this in a blog post http://sqlwithmanoj.com/tag/json-sql/[/url].

    With CTP3 it will support full JSON feature, documented here in MSDN blog: http://blogs.msdn.com/b/jocapc/archive/2015/05/16/json-support-in-sql-server-2016.aspx

  • manub22 (6/4/2015)


    Yes Jeff,

    The currently released version of SQL Server 2016 is CPT2, where you can only export SQL table rows in JSON string format.

    I've put this in a blog post http://sqlwithmanoj.com/2015/06/01/working-with-json-data-and-sql-queries-sql-server-2016/[/url].

    With CTP3 it will support full JSON feature, documented here in MSDN blog: http://blogs.msdn.com/b/jocapc/archive/2015/05/16/json-support-in-sql-server-2016.aspx

    CTPs aren't production releases. Anyone that uses a CTP for production is taking a fairly large risk. The product isn't officially "released" until the RTM comes out.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • JeeTee (11/6/2014)


    Depending on what's in between ElasticSearch and SQL, a rudimentary .Net application can quite easily de-serialize the JSON before putting it into SQL.

    exactly how i do it myself.

    Also, there's an article here on SSC to put that kind of library into a CLR (Common Language Runtime, so you cna do the work there.

    JSON.SQL: A CLR-resident JSON serializer/deserializer for SQL [/url]Server

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I have half of a how-to article written on how to do this with Powershell, which works great with Powershell 3.0+ and SQL 2k8 +.

    If anyone is interested, I can finalize the verbiage and attempt to submit it.

  • Since I asked a question, I just couldn't find a native to sql server solution that would work and will be easy to implement.

    My data (json) is coming from ElasticSearch.

    There is many articles on how to move data from sql server to ES, but non on how to go the other way.

    I ended up querying ES, parsing JSON and populating sql server in Python.

  • Below are some useful articles which will help you to start.
    1 - SSIS JSON
    2-SSIS ElasticSearch

    Hope this helps You!

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply