{"id":78695,"date":"2018-05-09T16:29:51","date_gmt":"2018-05-09T16:29:51","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=78695"},"modified":"2021-09-29T16:21:02","modified_gmt":"2021-09-29T16:21:02","slug":"importing-json-collections-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/importing-json-collections-sql-server\/","title":{"rendered":"Importing JSON Collections into SQL Server"},"content":{"rendered":"

It is fairly easy to Import JSON collections of documents into SQL Server if there is an underlying \u2018explicit\u2019 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.<\/p>\n

In this article we\u2019ll 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\u2019ll then import a single JSON Document, filling the ten tables with the data of 70,000 fake records from it.<\/p>\n

Let’s start this gently, putting simple collections into strings which we will insert into a table. We\u2019ll 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\u2019t 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.<\/p>\n

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<\/a>.<\/p>\n

Converting Simple JSON Arrays of Objects to Table-sources<\/h2>\n

We will start off by creating a simple table that we want to import into.<\/p>\n

  DROP TABLE IF EXISTS SheepCountingWords \r\n  CREATE TABLE SheepCountingWords\r\n    (\r\n    Number INT NOT NULL,\r\n    Word VARCHAR(40) NOT NULL,\r\n    Region VARCHAR(40) NOT NULL,\r\n    CONSTRAINT NumberRegionKey PRIMARY KEY  (Number,Region)\r\n    );\r\n  GO<\/pre>\n
We then choose a simple JSON Format<\/p>\n
  [{\r\n    \"number\": 11,  \"word\": \"Yan-a-dik\"\r\n  }, {\r\n    \"number\": 12,  \"word\": \"Tan-a-dik\"\r\n  }, {\r\n    \"number\": 13,  \"word\": \"Tethera-dik\"\r\n  }, {\r\n    \"number\": 14,  \"word\": \"Pethera-dik\"\r\n  }, {\r\n    \"number\": 15,  \"word\": \"Bumfit\"\r\n  }, {\r\n    \"number\": 16,  \"word\": \"Yan-a-bumtit\"\r\n  }, {\r\n    \"number\": 17,  \"word\": \"Tan-a-bumfit\"\r\n  }, {\r\n    \"number\": 18,  \"word\": \"Tethera-bumfit\"\r\n  }, {\r\n    \"number\": 19,  \"word\": \"Pethera-bumfit\"\r\n  }, {\r\n    \"number\": 20,  \"word\": \"Figgot\"\r\n  }] <\/pre>\n
We can very easily use OpenJSON to create a table-source that reflects the contents.<\/p>\n
  SELECT  Number, Word\r\n      FROM\r\n      OpenJson('[{\r\n    \"number\": 11,  \"word\": \"Yan-a-dik\"\r\n  }, {\r\n    \"number\": 12,  \"word\": \"Tan-a-dik\"\r\n  }, {\r\n    \"number\": 13,  \"word\": \"Tethera-dik\"\r\n  }, {\r\n    \"number\": 14,  \"word\": \"Pethera-dik\"\r\n  }, {\r\n    \"number\": 15,  \"word\": \"Bumfit\"\r\n  }, {\r\n    \"number\": 16,  \"word\": \"Yan-a-bumtit\"\r\n  }, {\r\n    \"number\": 17,  \"word\": \"Tan-a-bumfit\"\r\n  }, {\r\n    \"number\": 18,  \"word\": \"Tethera-bumfit\"\r\n  }, {\r\n    \"number\": 19,  \"word\": \"Pethera-bumfit\"\r\n  }, {\r\n    \"number\": 20,  \"word\": \"Figgot\"\r\n  }] '\r\n  )WITH (Number INT '$.number', Word VARCHAR(30) '$.word')<\/pre>\n
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\u2019ll create a temporary<\/em> procedure to insert the JSON data into the table.<\/p>\n
  DROP PROCEDURE IF EXISTS #MergeJSONwithCountingTable;\r\n   GO\r\n  CREATE PROCEDURE #MergeJSONwithCountingTable @json NVARCHAR(MAX),\r\n    @source NVARCHAR(MAX)\r\n  \/**\r\n  Summary: >\r\n    This inserts, or updates, into a table (dbo.SheepCountingWords) a JSON string consisting \r\n    of sheep-counting words for numbers between one and twenty used traditionally by sheep\r\n    farmers in Gt Britain and Brittany. it allows records to be inserted or updated in any\r\n    order or quantity.\r\n    \r\n  Author: PhilFactor\r\n  Date: 20\/04\/2018\r\n  Database: CountingSheep\r\n  Examples:\r\n     - EXECUTE #MergeJSONwithCountingTable @json=@OneToTen, @Source='Lincolnshire'\r\n     - EXECUTE #MergeJSONwithCountingTable @Source='Lincolnshire', @json='[{\r\n       \"number\": 11, \"word\": \"Yan-a-dik\"}, {\"number\": 12, \"word\": \"Tan-a-dik\"}]'\r\n  Returns: >\r\n    nothing\r\n  **\/\r\n  AS\r\n  MERGE dbo.SheepCountingWords AS target\r\n  USING\r\n    (\r\n    SELECT DISTINCT Number, Word, @source\r\n      FROM\r\n      OpenJson(@json)\r\n      WITH (Number INT '$.number', Word VARCHAR(20) '$.word')\r\n    ) AS source (Number, Word, Region)\r\n  ON target.Number = source.Number AND target.Region = source.Region\r\n  WHEN MATCHED AND (source.Word <> target.Word) THEN\r\n    UPDATE SET target.Word = source.Word\r\n  WHEN NOT MATCHED THEN \r\n    INSERT (Number, Word, Region)\r\n      VALUES\r\n        (source.Number, source.Word, source.Region);\r\n  GO<\/pre>\n
Now we try it out. Let’s assemble a couple of simple JSON strings from a table-source.<\/p>\n
  DECLARE @oneToTen NVARCHAR(MAX) =\r\n  \t(\r\n  \tSELECT LincolnshireCounting.number, LincolnshireCounting.word\r\n  \tFROM\r\n  \t\t(\r\n  \t\tVALUES (1, 'Yan'), (2, 'Tan'), (3, 'Tethera'), (4, 'Pethera'),\r\n  \t\t(5, 'Pimp'), (6, 'Sethera'), (7, 'Lethera'), (8, 'Hovera'),\r\n  \t\t(9, 'Covera'), (10, 'Dik')\r\n  \t\t) AS LincolnshireCounting (number, word)\r\n  \tFOR JSON AUTO\r\n  \t)\r\n  DECLARE @ElevenToTwenty NVARCHAR(MAX) =\r\n      (\r\n      SELECT LincolnshireCounting.number, LincolnshireCounting.word\r\n      FROM\r\n  \t\t(\r\n  \t\tVALUES (11, 'Yan-a-dik'), (12, 'Tan-a-dik'), (13, 'Tethera-dik'),\r\n  \t\t(14, 'Pethera-dik'), (15, 'Bumfit'), (16, 'Yan-a-bumtit'),\r\n  \t\t(17, 'Tan-a-bumfit'), (18, 'Tethera-bumfit'),\r\n  \t\t(19, 'Pethera-bumfit'), (20, 'Figgot')\r\n  \t\t) AS LincolnshireCounting (number, word)\r\n      FOR JSON AUTO\r\n      )<\/pre>\n
Now we can EXECUTE<\/strong> the procedure to store the Sheep-Counting Words in the table<\/p>\n
  EXECUTE #MergeJSONwithCountingTable @json=@ElevenToTwenty, @Source='Lincolnshire'\r\n  EXECUTE #MergeJSONwithCountingTable @json=@OneToTen, @Source='Lincolnshire'\r\n  --and make sure that we are protected against duplicate inserts\r\n  EXECUTE #MergeJSONwithCountingTable @Source='Lincolnshire', @json='[{\r\n    \"number\": 11, \"word\": \"Yan-a-dik\"}, {\"number\": 12, \"word\": \"Tan-a-dik\"}]'<\/pre>\n
Check to see that they were imported correctly by running this query:<\/p>\n
  SELECT * FROM SheepCountingWords<\/pre>\n
<\/p>\n
Converting to Table-source JSON Arrays of Objects that have Embedded Arrays<\/h2>\n
What if you want to import the sheep-counting words from several regions? So far, what we\u2019ve 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<\/a> will be whispering in your ear that there are two relations here, a region and the name for a number.<\/p>\n
Your JSON for a database of sheep-counting words will more likely look like this (I\u2019ve just reduced it to two numbers in the sequence<\/strong> array rather than the original twenty). Each JSON document in our collection has an embedded array.<\/p>\n
  [{\r\n     \"region\": \"Wilts\",\r\n     \"sequence\": [{\r\n        \"number\": 1,\r\n        \"word\": \"Ain\"\r\n     }, {\r\n        \"number\": 2,\r\n        \"word\": \"Tain\"\r\n     }]\r\n  }, {\r\n     \"region\": \"Scots\",\r\n     \"sequence\": [{\r\n        \"number\": 1,\r\n        \"word\": \"Yan\"\r\n     }, {\r\n        \"number\": 2,\r\n        \"word\": \"Tyan\"\r\n     }]\r\n  }]\r\n  *\/<\/pre>\n
After a bit of thought, we remember that the OpenJSON<\/strong> function actually allows you to put a JSON value in a column of the result. This means that you just need to CROSS APPLY<\/strong> each embedded array, passing to the \u2018cross-applied\u2019 OpenJSON<\/strong> function the JSON fragment representing the array, which it will then parse for you.<\/p>\n
  SELECT Number, Word, Region\r\n    FROM\r\n    OpenJson('[{\r\n     \"region\": \"Wilts\",\r\n     \"sequence\": [{\r\n        \"number\": 1,\r\n        \"word\": \"Ain\"\r\n     }, {\r\n        \"number\": 2,\r\n        \"word\": \"Tain\"\r\n     }]\r\n  }, {\r\n     \"region\": \"Scots\",\r\n     \"sequence\": [{\r\n        \"number\": 1,\r\n        \"word\": \"Yan\"\r\n     }, {\r\n        \"number\": 2,\r\n        \"word\": \"Tyan\"\r\n     }]\r\n  }]'       )\r\n    WITH (Region NVARCHAR(30) N'$.region', sequence NVARCHAR(MAX) N'$.sequence' AS JSON)\r\n      OUTER APPLY\r\n    OpenJson(sequence) --to get the number and word within each array element \r\n    WITH (Number INT N'$.number', Word NVARCHAR(30) N'$.word');<\/pre>\n
I haven\u2019t 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.<\/p>\n
    SELECT number, word, region\r\n    FROM\r\n    OpenJson('[{\r\n     \"region\": \"Wilts\",\r\n     \"sequence\": [{\r\n        \"number\": 1,\r\n        \"word\": \"Ain\"\r\n     }, {\r\n        \"number\": 2,\r\n        \"word\": \"Tain\"\r\n     }]\r\n  }, {\r\n     \"region\": \"Scots\",\r\n     \"sequence\": [{\r\n        \"number\": 1,\r\n        \"word\": \"Yan\"\r\n     }, {\r\n        \"number\": 2,\r\n        \"word\": \"Tyan\"\r\n     }]\r\n  }]'       )\r\n    WITH (region NVARCHAR(30), sequence NVARCHAR(MAX)  AS JSON)\r\n      OUTER APPLY\r\n    OpenJson(sequence) --to get the number and word within each array element \r\n    WITH (number INT, word NVARCHAR(30));<\/pre>\n
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\u2019ll 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:<\/p>\n
  DROP PROCEDURE IF EXISTS #MergeJSONWithEmbeddedArraywithCountingTable;\r\n  GO\r\n  CREATE PROCEDURE #MergeJSONWithEmbeddedArraywithCountingTable @json NVARCHAR(MAX)\r\n  \/**\r\n  Summary: >\r\n    This inserts, or updates, into a table (dbo.SheepCountingWords) a JSON collection \r\n    consisting of documents with an embedded array containing sheep-counting words for\r\n    numbers between one and twenty used traditionally by sheep farmers in Gt Britain and \r\n    Brittany. it allows records to be inserted or updated in any order or quantity.\r\n    \r\n  Author: PhilFactor\r\n  Date: 20\/04\/2018\r\n  Database: CountingSheep\r\n  Examples:\r\n     - EXECUTE #MergeJSONWithEmbeddedArraywithCountingTable @json=@AllTheRegions, \r\n     - EXECUTE #MergeJSONWithEmbeddedArraywithCountingTable @json='\r\n      [{\"region\":\"Wilts\",\"sequence\":[{\"number\":1,\"word\":\"Ain\"},{\"number\":2,\"word\":\"Tain\"}]},\r\n       {\"region\":\"Scots\",\"sequence\":[{\"number\":1,\"word\":\"Yan\"},{\"number\":2,\"word\":\"Tyan\"}]}]'\r\n  Returns: >\r\n    nothing\r\n  **\/\r\n  AS\r\n  MERGE dbo.SheepCountingWords AS target\r\n  USING\r\n    (\r\n    SELECT DISTINCT Number, Word, Region\r\n    FROM OpenJson(@json) \r\n    WITH (Region NVARCHAR(30) N'$.region', sequence NVARCHAR(MAX) N'$.sequence' AS JSON)\r\n      OUTER APPLY\r\n    OpenJson(sequence)\r\n    WITH (Number INT N'$.number', Word NVARCHAR(30) N'$.word')\r\n    ) AS source (Number, Word, Region)\r\n  ON target.Number = source.Number AND target.Region = source.Region\r\n  WHEN MATCHED AND (source.Word <> target.Word) THEN\r\n    UPDATE SET target.Word = source.Word\r\n  WHEN NOT MATCHED THEN INSERT (Number, Word, Region)\r\n                        VALUES\r\n                          (source.Number, source.Word, source.Region);\r\n  GO<\/pre>\n
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<\/p>\n
  DECLARE @JSON nvarchar(max)\r\n  SELECT @json = BulkColumn\r\n   FROM OPENROWSET (BULK 'D:\\raw data\\YanTanTethera.json', SINGLE_BLOB) as jsonFile\r\n   EXECUTE #MergeJSONWithEmbeddedArraywithCountingTable @JSON\r\n  --The file must be UTF-16 Little Endian<\/pre>\n
We can now check that it is all in and correct<\/p>\n
  SELECT SheepCountingWords.Number,\r\n    Max(CASE WHEN SheepCountingWords.Region = 'Ancient British' THEN\r\n               SheepCountingWords.Word ELSE '' END\r\n       ) AS [Ancient British],\r\n    Max(CASE WHEN SheepCountingWords.Region = 'Borrowdale' THEN\r\n               SheepCountingWords.Word ELSE '' END\r\n       ) AS Borrowdale,\r\n    Max(CASE WHEN SheepCountingWords.Region = 'Bowland' THEN\r\n               SheepCountingWords.Word ELSE '' END\r\n       ) AS Bowland,\r\n    Max(CASE WHEN SheepCountingWords.Region = 'Breton' THEN\r\n               SheepCountingWords.Word ELSE '' END\r\n       ) AS Breton,\r\n  \/*\r\n  Many columns missing here. Full source of query included on GitHub  *\/\r\n    Max(CASE WHEN SheepCountingWords.Region = 'Wilts' THEN\r\n               SheepCountingWords.Word ELSE '' END\r\n       ) AS Wilts\r\n    FROM SheepCountingWords\r\n    GROUP BY SheepCountingWords.Number\r\n    ORDER BY SheepCountingWords.Number<\/pre>\n
Giving …<\/p>\n
<\/p>\n
Just as a side-note, this data was collected for this article in various places on the internet but mainly from Yan Tan Tethera<\/a>. 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.<\/p>\n
Importing a More Complex JSON Data Collection into a SQL Server Database<\/h2>\n
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.<\/p>\n
In any relational database, we can use two approaches to JSON data, we can accommodate it, meaning we treat it as an \u2018atomic\u2019 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.<\/p>\n