Forum Discussion

daniel1001a's avatar
daniel1001a
Copper Contributor
Sep 16, 2022

SP that extract records from JSON and save/update the table record

I am trying to create a stored procedure that can:

  • Take JSON as input and extract the records in the JSON object to further update or create the record

  • When the record existed in the DB, update the record, if not, create a new record

  • Field Name in JSON is different from the column name in that table so we have to assign them.

Example JSON as input:

{
   "Ticketid":"0001",
   "TicketName":"DAILYTICKET01011",
   "Employee":{
      [
         {
            "employeeid":"1",   // in db, its EmpID
            "employeename":"David", //in db, it EmpName
            "totalpoint":"8"    //in db, it TotalPoint
         },
         {
            "employeeid":"5",   // in db, its EmpID
            "employeename":"Mike", //in db, it EmpName
            "totalpoint":"2"    //in db, it TotalPoint
         }
      ]
   },
   "Task":{
      [
         {
            "taskid":"1",
            "taskname":"Task01",
            "employeeid": 2,
            "size":1
         },
         {
            "taskid":"4",
            "taskname":"Task50",
            "employeeid": 1,
            "size":5
         }
      ]
   }
}

Example database: there are 2 existing tables, Emp and Task, they have one-2-many relationship.

Emp table:

EmpId      EmpName      TotalPoint
----------------------------------
1           David         3.0
2           Marry         3.3
3           Jason         2.4
4           Eric          4.3

Task table:

TaskId   TaskName      EmpId    TaskSize
-----------------------------------------
1         Task01         3        3.0
2         Task23         1        3.3
3         Task08         4        8.0

After executing the stored procedure, it will take that JSON input and our two tables are now become. It is either updating or creating against out tables.

Emp table:

EmpId      EmpName      TotalPoint
-----------------------------------
1           David         8.0
2           Marry         3.3
3           Jason         2.4
4           Eric          4.3
5           Mike          2.0

Task table:

TaskId   TaskName      EmpId    TaskSize
----------------------------------------
1         Task01         2        1.0
2         Task23         1        3.3
3         Task08         4        8.0
4         Task50         1        5.0
  • LainRobertson's avatar
    LainRobertson
    Silver Contributor

    daniel1001a 

     

    Hi, Daniel.

     

    I haven't played with JSON directly within SQL before, but here's a very, very basic working example.

     

    There's no error checking or validation here and is based off your JSON data sample, which I've included again for completeness.

     

    There's also no short-circuiting logic, meaning an UPDATE will always run for any row included within the JSON, even if none of the columns have changed value. This may or may not be of interest depending on the volume, but I would invest in doing this is if I were putting such a thing into production. However, it's beyond what I'm going to cover here as a guide.

     

    Lastly, I've used a table variable as a convenience in this example. However, as noted in the official literature, this is not necessarily recommended if you anticipate processing more than 100 rows on a regular basis.

     

     

    I would strongly urge you to put more effort into a production implementation but this should at least get you heading in the right direction.

     

     

    Sample tables

    CREATE TABLE [dbo].[emp](
    	[EmpId] [int] NOT NULL PRIMARY KEY,
    	[EmpName] [varchar](64) NOT NULL,
    	[TotalPoint] [float] NOT NULL,
    );
    GO
    
    CREATE TABLE [dbo].[task](
    	[TaskId] [int] NOT NULL PRIMARY KEY,
    	[TaskName] [varchar](64) NOT NULL,
    	[EmpId] [int] NOT NULL,
    	[TaskSize] [float] NOT NULL,
    );
    GO

     

    Stored procedure definition

    CREATE PROCEDURE dbo.UpdateEmployeesAndTasks
    (
    	@json AS [nvarchar](max)
    )
    AS
    BEGIN
    	SET NOCOUNT ON;
    	-- Topic #1: Employees.
    	-- Declare the table variable definition for employees, noting table variables aren't recommended where it's expected that more than 100 rows would routinely exist.
    	DECLARE @Employees table
    	(
    		employeeId [int] NOT NULL
    		, employeeName [varchar](64) NOT NULL
    		, totalPoint [float] NOT NULL
    		, existingEmpId [int] NULL
    	);
    
    	-- Toss the relevant data into the table variable.
    	INSERT INTO @Employees
    		SELECT
    			[json_e].employeeid AS [employeeId]
    			, [json_e].employeename AS [employeeName]
    			, [json_e].totalpoint AS [totalPoint]
    			, e.EmpId AS [existingEmpId]
    		FROM
    			OPENJSON(@json)
    			WITH
    			(
    				Employee nvarchar(max) AS JSON
    			)
    			OUTER APPLY OPENJSON(Employee)
    			WITH
    			(
    				employeeid int
    				, employeename varchar(64)
    				, totalpoint float
    			) AS [json_e]
    			LEFT OUTER JOIN dbo.emp AS e ON [json_e].employeeid = e.EmpId;
    
    	-- Perform relevant updates of existing employees.
    	UPDATE [emp]
    		SET
    			[EmpName] = e.[employeeName]
    			, [TotalPoint] = e.[totalPoint]
    	FROM
    		@Employees AS e
    	WHERE
    		e.existingEmpId IS NOT NULL
    		AND [EmpId] = e.existingEmpId;
    
    	-- Add any new employees.
    	INSERT INTO dbo.emp (EmpId, EmpName, TotalPoint)
    		SELECT
    			e.employeeId
    			, e.employeeName
    			, e.totalPoint
    		FROM
    			@Employees AS e
    		WHERE
    			e.existingEmpId IS NULL;
    
    
    	-- Topic #2: Tasks.
    	-- Declare the table variable definition for tasks, noting table variables aren't recommended where it's expected that more than 100 rows would routinely exist.
    	DECLARE @Tasks table
    	(
    		taskId [int] NOT NULL
    		, taskName [varchar](64) NOT NULL
    		, empId [int] NOT NULL
    		, taskSize [float] NOT NULL
    		, existingTaskId [int] NULL
    	);
    
    	-- Toss the relevant data into the table variable.
    	INSERT INTO @Tasks
    		SELECT
    			[json_t].taskid AS [taskId]
    			, [json_t].taskname AS [taskName]
    			, [json_t].employeeid AS [empId]
    			, [json_t].size AS [taskSize]
    			, t.TaskId AS [existingTaskId]
    		FROM
    			OPENJSON(@json)
    			WITH
    			(
    				Task nvarchar(max) AS JSON
    			)
    			OUTER APPLY OPENJSON(Task)
    			WITH
    			(
    				taskid int
    				, taskname varchar(64)
    				, employeeid int
    				, size float
    			) AS [json_t]
    			LEFT OUTER JOIN dbo.task AS t ON [json_t].taskid = t.TaskId;
    
    	-- Perform relevant updates of existing tasks.
    	UPDATE [task]
    		SET
    			[TaskName] = t.[taskName]
    			, [EmpId] = t.[empId]
    			, [TaskSize] = t.[taskSize]
    	FROM
    		@Tasks AS t
    	WHERE
    		t.existingTaskId IS NOT NULL
    		AND [task].[TaskId] = t.existingTaskId;
    
    	-- Add any new tasks.
    	INSERT INTO dbo.task (TaskId, TaskName, EmpId, TaskSize)
    		SELECT
    			t.taskId
    			, t.taskName
    			, t.empId
    			, t.taskSize
    		FROM
    			@Tasks AS t
    		WHERE
    			t.existingTaskId IS NULL;
    
    	SET NOCOUNT OFF;
    END;
    GO

     

    JSON sample data and EXEC example

    DECLARE @json [nvarchar](max) = N'
    {
    	"Ticketid":"0001",
    	"TicketName":"DAILYTICKET01011",
    	"Employee": [
    		{
    			"employeeid":"1",
    			"employeename":"David",
    			"totalpoint":"8"
    		},
    		{
    			"employeeid":"5",
    			"employeename":"Mike",
    			"totalpoint":"2"
    		}
    	],
    	"Task": [
    		{
    			"taskid":"1",
    			"taskname":"Task01",
    			"employeeid": 2,
    			"size":1
    		},
    		{
    			"taskid":"4",
    			"taskname":"Task50",
    			"employeeid": 1,
    			"size":5
    		}
    	]
    }
    ';
    
    EXEC dbo.UpdateEmployeesAndTasks @json;

     

    You can change the values around in the JSON and re-run the query to see the impact on the two tables.

     

    Cheers,

    Lain

    • GabrielBedard's avatar
      GabrielBedard
      Copper Contributor
      Thank you Lain for this answer. I was having the same problem. It helps me greatly.

Resources