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
– Subliminal Hash, Commented Mar 30, 2016 at 13:59
You could also use the built-in SQL Server functions msdn.microsoft.com/en-gb/library/dn921897.aspx — ChrisS
– ChrisS, Commented Mar 30, 2016 at 14:37
You could deserialize your JSON as a DataTable then upload to SQL server. See newtonsoft.com/json/help/html/DeserializeDataSet.htm and stackoverflow.com/questions/9075159/… — dbc
– dbc, Commented Mar 30, 2016 at 16:19

alessalessio · Accepted Answer · 2016-03-30 15:38:11Z

2

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;

answered Mar 30, 2016 at 15:38

alessalessio

1,2341 gold badge15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Read and Parse JSON data for SQL database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related