• Home
  • About Us
  • Contact Us
  • Privacy Policy
  • Special Offers
Business Intelligence Info
  • Business Intelligence
    • BI News and Info
    • Big Data
    • Mobile and Cloud
    • Self-Service BI
  • CRM
    • CRM News and Info
    • InfusionSoft
    • Microsoft Dynamics CRM
    • NetSuite
    • OnContact
    • Salesforce
    • Workbooks
  • Data Mining
    • Pentaho
    • Sisense
    • Tableau
    • TIBCO Spotfire
  • Data Warehousing
    • DWH News and Info
    • IBM DB2
    • Microsoft SQL Server
    • Oracle
    • Teradata
  • Predictive Analytics
    • FICO
    • KNIME
    • Mathematica
    • Matlab
    • Minitab
    • RapidMiner
    • Revolution
    • SAP
    • SAS/SPSS
  • Humor

Importing JSON Collections into SQL Server

June 1, 2018   BI News and Info

It is fairly easy to Import JSON collections of documents into SQL Server if there is an underlying ‘explicit’ table schema available to them. If each of the documents have different schemas, then you have little chance. Fortunately, schema-less data collections are rare.

In this article we’ll start simply and work through a couple of sample examples before ending by creating a SQL server database schema with ten tables, constraints and keys. Once those are in place we’ll then import a single JSON Document, filling the ten tables with the data of 70,000 fake records from it.

Let’s start this gently, putting simple collections into strings which we will insert into a table. We’ll then try slightly trickier JSON documents with embedded arrays and so on. We’ll start by using the example of sheep-counting words, collected from many different parts of Great Britain and Brittany. The simple aim is to put them into a table. I don’t use Sheep-counting words because they are of general importance but because they can be used to represent whatever data you are trying to import.

You will need access to SQL Server version, 2016 and later or Azure SQL Database or Warehouse to play along and you can download data and code from GitHub.

Converting Simple JSON Arrays of Objects to Table-sources

We will start off by creating a simple table that we want to import into.

  DROP TABLE IF EXISTS SheepCountingWords

  CREATE TABLE SheepCountingWords

    (

    Number INT NOT NULL,

    Word VARCHAR(40) NOT NULL,

    Region VARCHAR(40) NOT NULL,

    CONSTRAINT NumberRegionKey PRIMARY KEY  (Number,Region)

    );

  GO

We then choose a simple JSON Format

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

  [{

    “number”: 11,  “word”: “Yan-a-dik”

  }, {

    “number”: 12,  “word”: “Tan-a-dik”

  }, {

    “number”: 13,  “word”: “Tethera-dik”

  }, {

    “number”: 14,  “word”: “Pethera-dik”

  }, {

    “number”: 15,  “word”: “Bumfit”

  }, {

    “number”: 16,  “word”: “Yan-a-bumtit”

  }, {

    “number”: 17,  “word”: “Tan-a-bumfit”

  }, {

    “number”: 18,  “word”: “Tethera-bumfit”

  }, {

    “number”: 19,  “word”: “Pethera-bumfit”

  }, {

    “number”: 20,  “word”: “Figgot”

  }]

We can very easily use OpenJSON to create a table-source that reflects the contents.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

  SELECT  Number, Word

      FROM

      OpenJson(‘[{

    “number”: 11,  “word”: “Yan-a-dik”

  }, {

    “number”: 12,  “word”: “Tan-a-dik”

  }, {

    “number”: 13,  “word”: “Tethera-dik”

  }, {

    “number”: 14,  “word”: “Pethera-dik”

  }, {

    “number”: 15,  “word”: “Bumfit”

  }, {

    “number”: 16,  “word”: “Yan-a-bumtit”

  }, {

    “number”: 17,  “word”: “Tan-a-bumfit”

  }, {

    “number”: 18,  “word”: “Tethera-bumfit”

  }, {

    “number”: 19,  “word”: “Pethera-bumfit”

  }, {

    “number”: 20,  “word”: “Figgot”

  }] ‘

  )WITH (Number INT ‘$ .number’, Word VARCHAR(30) ‘$ .word’)

Once you have a table source, the quickest way to insert JSON into a table will always be the straight insert, even after an existence check. It is a good practice to make the process idempotent by only inserting the records that don’t already exist. I’ll use the MERGE statement just to keep things simple, though the left outer join with a null check is faster. The MERGE is often more convenient because it will accept a table-source such as a result from the OpenJSON function. We’ll create a temporary procedure to insert the JSON data into the table.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

  DROP PROCEDURE IF EXISTS #MergeJSONwithCountingTable;

   GO

  CREATE PROCEDURE #MergeJSONwithCountingTable @json NVARCHAR(MAX),

    @source NVARCHAR(MAX)

  /**

  Summary: >

    This inserts, or updates, into a table (dbo.SheepCountingWords) a JSON string consisting

    of sheep-counting words for numbers between one and twenty used traditionally by sheep

    farmers in Gt Britain and Brittany. it allows records to be inserted or updated in any

    order or quantity.

    

  Author: PhilFactor

  Date: 20/04/2018

  Database: CountingSheep

  Examples:

     – EXECUTE #MergeJSONwithCountingTable @json=@OneToTen, @Source=’Lincolnshire’

     – EXECUTE #MergeJSONwithCountingTable @Source=’Lincolnshire’, @json='[{

       “number”: 11, “word”: “Yan-a-dik”}, {“number”: 12, “word”: “Tan-a-dik”}]’

  Returns: >

    nothing

  **/

  AS

  MERGE dbo.SheepCountingWords AS target

  USING

    (

    SELECT DISTINCT Number, Word, @source

      FROM

      OpenJson(@json)

      WITH (Number INT ‘$ .number’, Word VARCHAR(20) ‘$ .word’)

    ) AS source (Number, Word, Region)

  ON target.Number = source.Number AND target.Region = source.Region

  WHEN MATCHED AND (source.Word <> target.Word) THEN

    UPDATE SET target.Word = source.Word

  WHEN NOT MATCHED THEN

    INSERT (Number, Word, Region)

      VALUES

        (source.Number, source.Word, source.Region);

  GO

Now we try it out. Let’s assemble a couple of simple JSON strings from a table-source.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

  DECLARE @oneToTen NVARCHAR(MAX) =

   (

   SELECT LincolnshireCounting.number, LincolnshireCounting.word

   FROM

   (

   VALUES (1, ‘Yan’), (2, ‘Tan’), (3, ‘Tethera’), (4, ‘Pethera’),

   (5, ‘Pimp’), (6, ‘Sethera’), (7, ‘Lethera’), (8, ‘Hovera’),

   (9, ‘Covera’), (10, ‘Dik’)

   ) AS LincolnshireCounting (number, word)

   FOR JSON AUTO

   )

  DECLARE @ElevenToTwenty NVARCHAR(MAX) =

      (

      SELECT LincolnshireCounting.number, LincolnshireCounting.word

      FROM

   (

   VALUES (11, ‘Yan-a-dik’), (12, ‘Tan-a-dik’), (13, ‘Tethera-dik’),

   (14, ‘Pethera-dik’), (15, ‘Bumfit’), (16, ‘Yan-a-bumtit’),

   (17, ‘Tan-a-bumfit’), (18, ‘Tethera-bumfit’),

   (19, ‘Pethera-bumfit’), (20, ‘Figgot’)

   ) AS LincolnshireCounting (number, word)

      FOR JSON AUTO

      )

Now we can EXECUTE the procedure to store the Sheep-Counting Words in the table

  EXECUTE #MergeJSONwithCountingTable @json=@ElevenToTwenty, @Source=‘Lincolnshire’

  EXECUTE #MergeJSONwithCountingTable @json=@OneToTen, @Source=‘Lincolnshire’

  –and make sure that we are protected against duplicate inserts

  EXECUTE #MergeJSONwithCountingTable @Source=‘Lincolnshire’, @json=‘[{

    “number”: 11, “word”: “Yan-a-dik”}, {“number”: 12, “word”: “Tan-a-dik”}]’

Check to see that they were imported correctly by running this query:

  SELECT * FROM SheepCountingWords

word image Importing JSON Collections into SQL Server

Converting to Table-source JSON Arrays of Objects that have Embedded Arrays

What if you want to import the sheep-counting words from several regions? So far, what we’ve been doing is fine for a collection that models a single table. However, real life isn’t like that. Not even Sheep-Counting Words are like that. A little internalized Chris Date will be whispering in your ear that there are two relations here, a region and the name for a number.

Your JSON for a database of sheep-counting words will more likely look like this (I’ve just reduced it to two numbers in the sequence array rather than the original twenty). Each JSON document in our collection has an embedded array.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

  [{

     “region”: “Wilts”,

     “sequence”: [{

        “number”: 1,

        “word”: “Ain”

     }, {

        “number”: 2,

        “word”: “Tain”

     }]

  }, {

     “region”: “Scots”,

     “sequence”: [{

        “number”: 1,

        “word”: “Yan”

     }, {

        “number”: 2,

        “word”: “Tyan”

     }]

  }]

  */

After a bit of thought, we remember that the OpenJSON function actually allows you to put a JSON value in a column of the result. This means that you just need to CROSS APPLY each embedded array, passing to the ‘cross-applied’ OpenJSON function the JSON fragment representing the array, which it will then parse for you.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

  SELECT Number, Word, Region

    FROM

    OpenJson(‘[{

     “region”: “Wilts”,

     “sequence”: [{

        “number”: 1,

        “word”: “Ain”

     }, {

        “number”: 2,

        “word”: “Tain”

     }]

  }, {

     “region”: “Scots”,

     “sequence”: [{

        “number”: 1,

        “word”: “Yan”

     }, {

        “number”: 2,

        “word”: “Tyan”

     }]

  }]’       )

    WITH (Region NVARCHAR(30) N‘$ .region’, sequence NVARCHAR(MAX) N‘$ .sequence’ AS JSON)

      OUTER APPLY

    OpenJson(sequence) –to get the number and word within each array element

    WITH (Number INT N‘$ .number’, Word NVARCHAR(30) N‘$ .word’);

I haven’t found the fact documented anywhere, but you can leave out the path elements from the column declaration of the WITH statement if the columns are exactly the same as the JSON keys, with matching case.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

    SELECT number, word, region

    FROM

    OpenJson(‘[{

     “region”: “Wilts”,

     “sequence”: [{

        “number”: 1,

        “word”: “Ain”

     }, {

        “number”: 2,

        “word”: “Tain”

     }]

  }, {

     “region”: “Scots”,

     “sequence”: [{

        “number”: 1,

        “word”: “Yan”

     }, {

        “number”: 2,

        “word”: “Tyan”

     }]

  }]’       )

    WITH (region NVARCHAR(30), sequence NVARCHAR(MAX)  AS JSON)

      OUTER APPLY

    OpenJson(sequence) –to get the number and word within each array element

    WITH (number INT, word NVARCHAR(30));

The ability to drill into sub-arrays by cross-joining OpenJSON function calls allows us to easily insert a large collection with a number of documents that have embedded arrays. This is looking a lot more like something that could, for example, tackle the import of a MongoDB collection as long as it was exported as a document array with commas between documents. I’ll include, with the download on GitHub, the JSON file that contains all the sheep-counting words that have been collected. Here is the updated stored procedure:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

  DROP PROCEDURE IF EXISTS #MergeJSONWithEmbeddedArraywithCountingTable;

  GO

  CREATE PROCEDURE #MergeJSONWithEmbeddedArraywithCountingTable @json NVARCHAR(MAX)

  /**

  Summary: >

    This inserts, or updates, into a table (dbo.SheepCountingWords) a JSON collection

    consisting of documents with an embedded array containing sheep-counting words for

    numbers between one and twenty used traditionally by sheep farmers in Gt Britain and

    Brittany. it allows records to be inserted or updated in any order or quantity.

    

  Author: PhilFactor

  Date: 20/04/2018

  Database: CountingSheep

  Examples:

     – EXECUTE #MergeJSONWithEmbeddedArraywithCountingTable @json=@AllTheRegions,

     – EXECUTE #MergeJSONWithEmbeddedArraywithCountingTable @json=’

      [{“region”:”Wilts”,”sequence”:[{“number”:1,”word”:”Ain”},{“number”:2,”word”:”Tain”}]},

       {“region”:”Scots”,”sequence”:[{“number”:1,”word”:”Yan”},{“number”:2,”word”:”Tyan”}]}]’

  Returns: >

    nothing

  **/

  AS

  MERGE dbo.SheepCountingWords AS target

  USING

    (

    SELECT DISTINCT Number, Word, Region

    FROM OpenJson(@json)

    WITH (Region NVARCHAR(30) N‘$ .region’, sequence NVARCHAR(MAX) N‘$ .sequence’ AS JSON)

      OUTER APPLY

    OpenJson(sequence)

    WITH (Number INT N‘$ .number’, Word NVARCHAR(30) N‘$ .word’)

    ) AS source (Number, Word, Region)

  ON target.Number = source.Number AND target.Region = source.Region

  WHEN MATCHED AND (source.Word <> target.Word) THEN

    UPDATE SET target.Word = source.Word

  WHEN NOT MATCHED THEN INSERT (Number, Word, Region)

                        VALUES

                          (source.Number, source.Word, source.Region);

  GO

We can now very quickly ingest the whole collection into our table, pulling the data in from file. We include this file with the download on GitHub, so you can try it out. There are thirty-three different regions in the JSON file

  DECLARE @JSON nvarchar(max)

  SELECT @json = BulkColumn

   FROM OPENROWSET (BULK ‘D:\raw data\YanTanTethera.json’, SINGLE_BLOB) as jsonFile

   EXECUTE #MergeJSONWithEmbeddedArraywithCountingTable @JSON

  –The file must be UTF-16 Little Endian

We can now check that it is all in and correct

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

  SELECT SheepCountingWords.Number,

    Max(CASE WHEN SheepCountingWords.Region = ‘Ancient British’ THEN

               SheepCountingWords.Word ELSE ” END

       ) AS [Ancient British],

    Max(CASE WHEN SheepCountingWords.Region = ‘Borrowdale’ THEN

               SheepCountingWords.Word ELSE ” END

       ) AS Borrowdale,

    Max(CASE WHEN SheepCountingWords.Region = ‘Bowland’ THEN

               SheepCountingWords.Word ELSE ” END

       ) AS Bowland,

    Max(CASE WHEN SheepCountingWords.Region = ‘Breton’ THEN

               SheepCountingWords.Word ELSE ” END

       ) AS Breton,

  /*

  Many columns missing here. Full source of query included on GitHub  */

    Max(CASE WHEN SheepCountingWords.Region = ‘Wilts’ THEN

               SheepCountingWords.Word ELSE ” END

       ) AS Wilts

    FROM SheepCountingWords

    GROUP BY SheepCountingWords.Number

    ORDER BY SheepCountingWords.Number

Giving …

word image 1 Importing JSON Collections into SQL Server

Just as a side-note, this data was collected for this article in various places on the internet but mainly from Yan Tan Tethera. Each table was pasted into Excel and tidied up. The JSON code was created by using three simple functions, one for the cell-level value, one for the row value and a final summation. This allowed simple adding, editing and deleting of data items. The technique is only suitable where columns are of fixed length.

Importing a More Complex JSON Data Collection into a SQL Server Database

We have successfully imported the very simplest JSON files into SQL Server. Now we need to consider those cases where the JSON document or collection represents more than one table.

In any relational database, we can use two approaches to JSON data, we can accommodate it, meaning we treat it as an ‘atomic’ unit and store the JSON unprocessed, or we can assimilate it, meaning that we turn the data into a relational format that can be easily indexed and accessed.

  • To accommodate JSON, we store it as a CLOB, usually NVARCHAR(MAX), with extra columns containing the extracted values for the data fields with which you would want to index the data. This is fine where all the database has to do is to store an application object without understanding it.
  • To assimilate JSON, we need to extract all the JSON data and store it in a relational form.

Our example represents a very simple customer database with ten linked tables. We will first accommodate the JSON document by creating a table (dbo.JSONDocuments) that merely stores, in each row, the reference to the customer, along with all the information about that customer, each aspect (addresses, phones, email addresses and so on) in separate columns as CLOB JSON strings.

We then use this table to successively assimilate each JSON column into the relational database.

This means that we need parse the full document only once.

To be clear about the contents of the JSON file, we will be cheating by using spoof data. We would never have unencrypted personal information in a database or a JSON file. Credit Card information would never be unencrypted. This data is generated entirely by SQL Data Generator, and the JSON collection contains 70,000 documents. The method of doing it is described here.

We’ll make other compromises. We’ll have no personal identifiers either. We will simply use the document order. In reality, the JSON would store the surrogate key of person_id.

The individual documents will look something like this

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

  {

      “Full Name” : “Mr Philip Fortescue Factor esq.”,

      “Name” : {

          “Title” : “Mr”,

          “First Name” : ” Philip”,

          “Middle Name” : ” Fortescue”,

          “Last Name” : “Factor”,

          “Suffix” : “Esq.”

      },

      “Addresses” : [

          {

              “type” : “Home”,

              “Full Address” : “83a Manor Parkway  Crook , York, Cornwall CF9 7MU”,

              “County” : “Cornwall”,

              “Dates” : {

                  “Moved In” : “2012-04-01T16:11:29.570″,

              }

          }

      ],

      “Notes” : [

          {

              “Text” : “you! your like to products. happy your management time) thank all plus service. Thank our our that as promise in”,

              “Date” : “2014-11-06T04:05:05.020″

          }

      ],

      “Phones” : [

          {

              “TypeOfPhone” : “Home”,

              “DiallingNumber” : “01593 611001″,

              “Dates” : {

                  “From” : “2004-10-20T15:57:47.480″

              }

          }

      ],

      “EmailAddresses” : [

          {

              “EmailAddress” : “Kian1@AAL.com”,

              “StartDate” : “2013-05-25″,

              “EndDate” : “2017-06-01″

          },

          {

              “EmailAddress” : “William1@Gmoul.com”,

              “StartDate” : “2017-06-31″

          }

      ],

      “Cards” : [

          {

              “CardNumber” : “4684453190680369”,

              “ValidFrom” : “2017-05-14″,

              “ValidTo” : “2026-01-26″,

              “CVC” : “262”

          },

          {

              “CardNumber” : “5548597043927766”,

              “ValidFrom” : “2017-03-16″,

              “ValidTo” : “2028-10-27″,

              “CVC” : “587”

          },

          {

              “CardNumber” : “4896940995709652”,

              “ValidFrom” : “2015-08-17″,

              “ValidTo” : “2022-12-03″,

              “CVC” : “220”

          }

      ]

  }

We will import this into a SQL Server database designed like this:

word image 2 Importing JSON Collections into SQL Server

The build script is included with the download on GitHub.

So, all we need now is the batch to import the JSON file that contains the collection and populate the table with the data. We will now describe individual parts of the batch.

We start out by reading the customersUTF16.json file into a variable.

  DECLARE @CJSON nvarchar(max)

  SELECT @Cjson = BulkColumn

   FROM OPENROWSET (BULK ‘D:\raw data\customersUTF16.json’, SINGLE_BLOB) as j

  –text encoding must be littlendian UTF16

The next step is to create a table at the document level, with the main arrays within each document represented by columns. (In some cases, there are sub-arrays. The phone numbers, for example, have an array of dates.) This means that this initial slicing of the JSON collection needs be done only once. In our case, there are

  • The details of the Name,
  • Addresses,
  • Credit Cards,
  • Email Addresses,
  • Notes,
  • Phone numbers

We fill this table via a call to openJSON. By doing this, we have the main details of each customer available to us when slicing up embedded arrays. The batch is designed so it can be rerun and should be idempotent. This means that there is less of a requirement to run the process in a single transaction.

1

2

3

4

5

6

7

8

9

10

11

12

13

  DROP TABLE IF EXISTS dbo.JSONDocuments;

  CREATE TABLE dbo.JSONDocuments

    (

    Document_id INT NOT NULL,

        [Full_Name] NVARCHAR(30) NOT NULL,

     Name NVARCHAR(MAX) NOT NULL,–holds a JSON object

     Addresses NVARCHAR(MAX) NULL,–holds an array of JSON objects

     Cards NVARCHAR(MAX) NULL,–holds an array of JSON objects

     EmailAddresses NVARCHAR(MAX) NULL,–holds an array of JSON objects

     Notes NVARCHAR(MAX) NULL,–holds an array of JSON objects

     Phones NVARCHAR(MAX) NULL,–holds an array of JSON objects

     CONSTRAINT JSONDocumentsPk PRIMARY KEY (Document_id)

    ) ON [PRIMARY];

Now we fill this table with a row for each document, each representing the entire date for a customer. Each item of root data, such as the id and the customer’s full name, is held as a column. All other columns hold JSON. This table will be an ‘accomodation’ to the JSON data, in that each row represents a customer, but each JSON document in the collection is shredded to provide a JSON string that represents the attributes and relations of that customer. We can now assimilate this data step-by-step

1

2

3

4

5

6

7

8

9

10

11

12

  INSERT INTO dbo.JSONDocuments ( Document_id,Full_name,[Name],Addresses, Cards, EmailAddresses, Notes, Phones)

   SELECT [key] AS Document_id,Full_name,[Name],Addresses, Cards, EmailAddresses, Notes, Phones

    FROM OpenJson(@CJSON) AS EachDocument

        CROSS APPLY OpenJson(EachDocument.Value)

     WITH (

         [Full_Name] NVARCHAR(30) N‘$ .”Full Name”‘,

     Name NVARCHAR(MAX) N‘$ .Name’ AS JSON,

     Addresses NVARCHAR(MAX) N‘$ .Addresses’ AS JSON,

     Cards NVARCHAR(MAX) N‘$ .Cards’ AS JSON,

     EmailAddresses NVARCHAR(MAX) N‘$ .EmailAddresses’ AS JSON,

     Notes NVARCHAR(MAX) N‘$ .Notes’ AS JSON,

     Phones NVARCHAR(MAX) N‘$ .Phones’ AS JSON)

First we need to create an entry in the person table if it doesn’t already exist, as that has the person_id. We need to do this first because otherwise the foreign key constraints will protest.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

  SET IDENTITY_INSERT [Customer].[Person] On

  MERGE [Customer].[Person] AS target

  USING

    (–get the required data for the person table and merge it with what is there

    SELECT JSONDocuments.Document_id, Title, FirstName,

         MiddleName, LastName, Suffix

      FROM dbo.JSONDocuments

        CROSS APPLY

      OpenJson(JSONDocuments.Name)

      WITH

        (

        Title NVARCHAR(8) N‘$ .Title’, FirstName VARCHAR(40) N‘$ .”First Name”‘,

        MiddleName VARCHAR(40) N‘$ .”Middle Name”‘,

        LastName VARCHAR(40) N‘$ .”Last Name”‘, Suffix VARCHAR(10) N‘$ .Suffix’

        )

    ) AS source (person_id, Title, FirstName, MiddleName, LastName, Suffix)

  ON target.person_id = source.person_id

  WHEN NOT MATCHED THEN

    INSERT (person_id, Title, FirstName, MiddleName, LastName, Suffix)

      VALUES

        (source.person_id, source.Title, source.FirstName,

     source.MiddleName, source.LastName, source.Suffix);

  SET IDENTITY_INSERT [Customer].[Person] Off

Now we do the notes. We’ll do this first because it is a bit awkward. This has the complication because there is a many to many relationship with the notes and the people, because the same standard notes can be associated with many customers such an overdue invoice payment etc. We’ll use a table variable to allow us to guard against inserting duplicate records.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

  DECLARE @Note TABLE (document_id INT NOT NULL, Text NVARCHAR(MAX) NOT NULL, Date DATETIME)

  INSERT INTO @Note (document_id, Text, Date)

    SELECT JSONDocuments.Document_id, Text, Date

      FROM dbo.JSONDocuments

        CROSS APPLY OpenJson(JSONDocuments.Notes) AS TheNotes

        CROSS APPLY

      OpenJson(TheNotes.Value)

      WITH (Text NVARCHAR(MAX) N‘$ .Text’, Date DATETIME N‘$ .Date’)

   WHERE Text IS NOT null

  –if the notes are new then insert them

  INSERT INTO Customer.Note (Note)

    SELECT DISTINCT newnotes.Text

      FROM @Note AS newnotes

        LEFT OUTER JOIN Customer.Note

          ON note.notestart = Left(newnotes.Text,850)–just compare the first 850 chars

      WHERE note.note IS NULL

  /* now fill in the many-to-many table relating notes to people, making sure that you

  –do not duplicate anything*/

  INSERT INTO Customer.NotePerson (Person_id, Note_id)

    SELECT newnotes.document_id, note.note_id

      FROM @Note AS newnotes

        INNER JOIN Customer.Note

          ON note.note = newnotes.Text

     LEFT OUTER JOIN Customer.NotePerson

       ON NotePerson.Person_id=newnotes.document_id

   AND NotePerson.note_id=note.note_id

   WHERE NotePerson.note_id IS null

Addresses are complicated because they involve three tables. There is the address, which is the physical place, the abode, which records when and why the person was associated with the place, and a third table which constrains the type of abode. We create a table variable to support the various queries without any extra shredding.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

  DECLARE @addresses TABLE

    (

    person_id INT NOT null,

    Type NVARCHAR(40) NOT null,

    Full_Address NVARCHAR(200)NOT null,

    County NVARCHAR(30) NOT null,

    Start_Date DATETIME NOT null,

    End_Date DATETIME null

    );

  –stock the table variable with the address information

  INSERT INTO @Addresses(person_id, Type,Full_Address, County, [Start_Date], End_Date)

  SELECT Document_id, Address.Type,Address.Full_Address, Address.County,

           WhenLivedIn.[Start_date],WhenLivedIn.End_date

      FROM dbo.JSONDocuments

        CROSS APPLY

      OpenJson(JSONDocuments.Addresses) AllAddresses

     CROSS APPLY

      OpenJson(AllAddresses.value)

      WITH

        (

        Type NVARCHAR(8) N‘$ .type’, Full_Address NVARCHAR(200) N‘$ .”Full Address”‘,

        County VARCHAR(40) N‘$ .County’,Dates NVARCHAR(MAX) AS json

        ) Address

      CROSS APPLY

   OpenJson(Address.Dates) WITH

        (

        Start_date datetime N‘$ .”Moved In”‘,End_date datetime N‘$ .”Moved Out”‘

        )WhenLivedIn

  –first make sure that the types of address exists and add if necessary

  INSERT INTO Customer.Addresstype (TypeOfAddress)

    SELECT DISTINCT NewAddresses.Type

      FROM @addresses AS NewAddresses

        LEFT OUTER JOIN Customer.Addresstype

          ON NewAddresses.Type = Addresstype.TypeOfAddress

      WHERE Addresstype.TypeOfAddress IS NULL;

  –Fill the Address table with addresses ensuring uniqueness

  INSERT INTO Customer.Address (Full_Address, County)

  SELECT DISTINCT NewAddresses.Full_Address, NewAddresses.County

    FROM @addresses AS NewAddresses

      LEFT OUTER JOIN Customer.Address AS currentAddresses

        ON NewAddresses.Full_Address = currentAddresses.Full_Address

    WHERE currentAddresses.Full_Address IS NULL;

  –and now the many-to-many Abode table

  INSERT INTO Customer.Abode (Person_id, Address_ID, TypeOfAddress, Start_date,

  End_date)

    SELECT newAddresses.person_id, address.Address_ID, newAddresses.Type,

      newAddresses.Start_Date, newAddresses.End_Date

      FROM @addresses AS newAddresses

        INNER JOIN customer.address

          ON newAddresses.Full_Address = address.Full_Address

        LEFT OUTER JOIN Customer.Abode

          ON Abode.person_id = newAddresses.person_id

         AND Abode.Address_ID = address.Address_ID

      WHERE Abode.person_id IS NULL;

Credit cards are much easier since they are a simple sub-array.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

  INSERT INTO customer.CreditCard (Person_id, CardNumber, ValidFrom, ValidTo, CVC)

    SELECT JSONDocuments.Document_id AS Person_id, new.CardNumber, new.ValidFrom,

      new.ValidTo, new.CVC

      FROM dbo.JSONDocuments

        CROSS APPLY OpenJson(JSONDocuments.Cards) AS TheCards

        CROSS APPLY

      OpenJson(TheCards.Value)

      WITH

        (

        CardNumber VARCHAR(20), ValidFrom DATE N‘$ .ValidFrom’,

        ValidTo DATE N‘$ .ValidTo’, CVC CHAR(3)

        ) AS new

        LEFT OUTER JOIN customer.CreditCard

          ON JSONDocuments.Document_id = CreditCard.Person_id

         AND new.CardNumber = CreditCard.CardNumber

      WHERE CreditCard.CardNumber IS NULL;

Email Addresses are also simple. We’re on the downhill slopes now.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

  INSERT INTO Customer.EmailAddress (Person_id, EmailAddress, StartDate, EndDate)

    SELECT JSONDocuments.Document_id AS Person_id, new.EmailAddress,

      new.StartDate, new.EndDate

      FROM dbo.JSONDocuments

        CROSS APPLY OpenJson(JSONDocuments.EmailAddresses) AS TheEmailAddresses

        CROSS APPLY

      OpenJson(TheEmailAddresses.Value)

      WITH

        (

        EmailAddress NVARCHAR(40) N‘$ .EmailAddress’,

        StartDate DATE N‘$ .StartDate’, EndDate DATE N‘$ .EndDate’

        ) AS new

        LEFT OUTER JOIN Customer.EmailAddress AS email

          ON JSONDocuments.Document_id = email.Person_id

         AND new.EmailAddress = email.EmailAddress

      WHERE email.EmailAddress IS NULL;

Now we add these customers phones. The various dates for the start and end of the use of the phone number are held in a subarray within the individual card objects. That makes things slightly more awkward

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

  DECLARE @phones TABLE

    (

    Person_id INT,

    TypeOfPhone NVARCHAR(40),

    DiallingNumber VARCHAR(20),

    Start_Date DATE,

    End_Date DATE

    );

  INSERT INTO @phones (Person_id, TypeOfPhone, DiallingNumber, Start_Date,

  End_Date)

    SELECT JSONDocuments.Document_id, EachPhone.TypeOfPhone,

      EachPhone.DiallingNumber, [From], [To]

      FROM dbo.JSONDocuments

        CROSS APPLY OpenJson(JSONDocuments.Phones) AS ThePhones

        CROSS APPLY

      OpenJson(ThePhones.Value)

      WITH

        (

        TypeOfPhone NVARCHAR(40), DiallingNumber VARCHAR(20), Dates NVARCHAR(MAX) AS JSON

        ) AS EachPhone

        CROSS APPLY

      OpenJson(EachPhone.Dates)

      WITH ([From] DATE, [To] DATE);

  –insert any new phone types

  INSERT INTO Customer.PhoneType (TypeOfPhone)

    SELECT DISTINCT new.TypeOfPhone

      FROM @phones AS new

        LEFT OUTER JOIN Customer.PhoneType

          ON PhoneType.TypeOfPhone = new.TypeOfPhone

      WHERE PhoneType.TypeOfPhone IS NULL AND new.TypeOfPhone IS NOT null;

  –insert all new phones

  INSERT INTO Customer.Phone (Person_id, TypeOfPhone, DiallingNumber, Start_date,

  End_date)

    SELECT new.Person_id, new.TypeOfPhone, new.DiallingNumber, new.Start_Date,

      new.End_Date

      FROM @phones AS new

        LEFT OUTER JOIN Customer.Phone

          ON Phone.DiallingNumber = new.DiallingNumber

         AND Phone.Person_id = new.Person_id

      WHERE Phone.Person_id IS NULL AND new.TypeOfPhone IS NOT null;

Conclusion

JSON support in SQL Server has been the result of a long wait, but now that we have it, it opens up several possibilities.

No SQL Server Developer or admin needs to rule out using JSON for ETL (Extract, Transform, Load) processes to pass data between JSON-based document databases and SQL Server. The features that SQL Server has are sufficient, and far easier to use than the SQL Server XML support.

A typical SQL Server database is far more complex than the simple example used in this article, but it is certainly not an outrageous idea that a database could have its essential static data drawn from JSON documents: These are more versatile than VALUE statements and more efficient than individual INSERT statements.

I’m inclined to smile on the idea of transferring data between the application and database as JSON. It is usually easier for front-end application programmers, and we Database folks can, at last, do all the checks and transformations to accommodate data within the arcane relational world, rather than insist on the application programmer doing it. It will also decouple the application and database to the extent that the two no longer would need to shadow each other in terms of revisions.

JSON collections of documents represent an industry-standard way of transferring data. It is today’s CSV, and it is good to know that SQL Server can support it.

Let’s block ads! (Why?)

SQL – Simple Talk

Collections, Importing, into, JSON, Server
  • Recent Posts

    • The Great Awakening?
    • Another Success Story: McWane
    • The Dynamics 365 Sales Mobile App Helps Salespeople Stay Productive From Anywhere
    • THEY CAN FIND THE GUY WHO BROKE A WINDOW BUT NOT A MURDERER?
    • TIBCO4Good and She Loves Data Offer Free Data Skills Workshops During a Time of Vulnerability
  • Categories

  • Archives

    • January 2021
    • December 2020
    • November 2020
    • October 2020
    • September 2020
    • August 2020
    • July 2020
    • June 2020
    • May 2020
    • April 2020
    • March 2020
    • February 2020
    • January 2020
    • December 2019
    • November 2019
    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • May 2019
    • April 2019
    • March 2019
    • February 2019
    • January 2019
    • December 2018
    • November 2018
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • June 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • November 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • April 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • October 2016
    • September 2016
    • August 2016
    • July 2016
    • June 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • November 2015
    • October 2015
    • September 2015
    • August 2015
    • July 2015
    • June 2015
    • May 2015
    • April 2015
    • March 2015
    • February 2015
    • January 2015
    • December 2014
    • November 2014
© 2021 Business Intelligence Info
Power BI Training | G Com Solutions Limited