Freigeben über

Generate stored procedure that imports array of JSON object in table

  • Artikel

OPENJSON function enables you to easily write simple statement that loads array of JSON objects in table. Example is:

 INSERT INTO dbo.People(Name, Surname)
SELECT Name, Surname
FROM OPENJSON (@json) WITH (Name nvarchar(100), Surname nvarchar(100))

See details in this post OPENJSON – The easiest way to import JSON text into table.

Although this is a simple command, it might be hard to write it if you have wide tables with 20-30 column. Also if some of the columns have special characters, you will need to surround them with [ ] in SQL names, and with " " in JSON paths.

Therefore, I have create a function that generates this script - you can download it here. This function looks like this:

 CREATE FUNCTION
dbo.GenerateJsonInsertProcedure(@SchemaName sysname, @TableName sysname, @JsonColumns nvarchar(max))
RETURNS NVARCHAR(MAX)

In order to generate Insert stored procedure, you can specify Schema name of your table and table name. Also if you have some columns in table that contain JSON text and if you will have some nested JSON in your input, you can specify list of these columns in @JsonColumns parameter.

Now, let's see hot it works. I will generate JSON insert stored procedure for Person.Address table:

 declare @SchemaName sysname = 'Person' --> Name of the table where we want to insert JSON
declare @TableName sysname = 'Address' --> Name of the table schema where we want to insert JSON
declare @JsonColumns nvarchar(max) = '||' --> List of pipe-separated NVARCHAR(MAX) column names that contain JSON text, e.g. '|AdditionalInfo|Demographics|'

 print (dbo.GenerateJsonInsertProcedure(@SchemaName, @TableName, @JsonColumns))

In this case I will just print script that function returns. Output will be:

 DROP PROCEDURE IF EXISTS [Person].[AddressInsertJson]
GO
CREATE PROCEDURE [Person].[AddressInsertJson](@Address NVARCHAR(MAX))
AS BEGIN
INSERT INTO Address([AddressLine1],[AddressLine2],[City],[StateProvinceID],[PostalCode],[ModifiedDate])
 SELECT [AddressLine1],[AddressLine2],[City],[StateProvinceID],[PostalCode],[ModifiedDate]
 FROM OPENJSON(@AddressJson)
 WITH (
 [AddressLine1] nvarchar(120) N'strict $."AddressLine1"',
 [AddressLine2] nvarchar(120) N'$."AddressLine2"',
 [City] nvarchar(60) N'strict $."City"',
 [StateProvinceID] int N'strict $."StateProvinceID"',
 [PostalCode] nvarchar(30) N'strict $."PostalCode"',
 [ModifiedDate] datetime N'strict $."ModifiedDate"')
END

Function will go through all columns in the specified table, check what is the type, is it required column (in that case it will generate $.strict modifier in path) and create script. You can modify this query and remove unnecessary columns if you want.

Script that generates insert procedure is here.

Comments

  • Anonymous
    April 05, 2016
    According to the latest release on Azure SQL Database(https://azure.microsoft.com/en-us/updates/public-preview-json-in-azure-sql-database/), it has been mentioned that JSON functionalities are available. However I see the OPENJSON does not work. JSON_VALUE, JSON_QUERY and JSON_MODIFY seem to be working though. Trying to get an update on it.
  • Anonymous
    April 09, 2016
    Hi Raj,OPENJSON is available in compatibility level 130 both in Azure SQL Database and SQL Server.Regards,Jovan
    • Anonymous
      May 03, 2017
      how to use json query in sql 2014