Read and Parse JSON data for SQL database

Question

I have a JSON file with a lot of data, and I want to put that JSON data into a SQL Server database. I am very new to this and I don't know where to start. I want to parse the JSON data in a way I can then send '{title:"""}' into my database column 'title', and so on for each column. Here is what the JSON data looks like.

{"success":true,"data":[
  {
    "Title": "text here",
    "Description": "text here",
    "Order Type": "text here",
    "Date": "text here"
  },
  {
    "Title": "text",
    "Description": "text",
    "Order Type": "text",
    "Date": "text"
  },

I'm still trying to figure out the best way to approach this but I'm in visual studio using webpages. I have JSON.NET but don't know where to start as far as decoding the json file. But from there how do I send each line in the object to the corresponding database column? Any advice would help as I see I need to go back to school lol

You should build a class with your desired properties. Then, cast your json to that object with JSON.NET. from there on, the db insert logic is the same — Subliminal Hash, Mar 30 '16 at 13:59
You could also use the built-in SQL Server functions https://msdn.microsoft.com/en-gb/library/dn921897.aspx — ChrisS, Mar 30 '16 at 14:37
You could deserialize your JSON as a `DataTable` then upload to SQL server. See http://www.newtonsoft.com/json/help/html/DeserializeDataSet.htm and http://stackoverflow.com/questions/9075159/how-to-insert-a-data-table-into-sql-server-database-table — dbc, Mar 30 '16 at 16:19

score 2 · Accepted Answer · answered Mar 30 '16 at 15:38

If you have SQL Server 2016, this may suit you:

SET @json =
N'[
      {"success":true,"data":[
        {
          "Title": "text here",
          "Description": "text here",
          "Order Type": "text here",
          "Date": "text here"
        },
        {
          "Title": "text",
          "Description": "text",
          "Order Type": "text",
          "Date": "text"
        }]
    }
]'

INSERT INTO YourTable
SELECT jsonData.*
FROM OPENJSON (@json, N'$.data')
          WITH (
             Title   varchar(200) N'$.data.Title', 
             Date     varchar(200)     N'$.data.Description',
             Customer varchar(200) N'$.data.Order Type', 
             Quantity varchar(200)          N'$.data.Date'
          )
 AS jsonData;

Read and Parse JSON data for SQL database

1 Answers1