JSON with SQL Server

JSON with SQL Server

1. Select from Json Data Object

--Select from Json String--
DECLARE @jsonEmp varchar(max) 
set @jsonEmp = '[
 { "id" : 1,"EmpName": "DK", "SurName": "Shaw", "DeptID": 25, "DOBirth": "2007-03-25T12:00:00", "Active": true },
 { "id" : 4,"EmpName": "Mick", "SurName": "smith", "DeptID": 35, "DOBirth": "2005-11-04T12:00:00", "Active": true },
 { "id" : 5,"EmpName": "Paul", "SurName": "Dang", "DeptID": 15, "DOBirth": "1983-10-28T12:00:00", "Active": false },
 { "id" : 7,"EmpName": "Mohan", "SurName": "Kumar", "DeptID": 12, "DOBirth": "1995-07-05T12:00:00", "Active": true },
 { "id" : 9,"EmpName": "Bhushan", "SurName": "Kal", "DeptID": 37, "DOBirth": "2015-03-25T12:00:00", "Active": true }]'
 
    SELECT * FROM OPENJSON(@jsonEmp)
    WITH (id int, EmpName nvarchar(50), SurName nvarchar(50),
 
                 DeptID int, DOBirth datetime2, Active bit) AS TEmployee

2. Select Database Table as Json Data output
 --Create Employees Table --
CREATE TABLE [dbo].[TEmployees](
 [EMPID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nchar](50) NOT NULL,
 [SurName] [nchar](50) NULL,
 [DataOfBirth] [date] NOT NULL,
 [Nationality] [nchar](3) NULL,
 [Salary] [float] NULL,
 [DEPTID] [int] NULL,
 [ActiveEmp] [bit] NULL,
 [InDate] [datetime] NULL,
 CONSTRAINT [PK_TEmployees] PRIMARY KEY CLUSTERED 
(
 [EMPID] ASC
)WITH (
PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[TEmployees] ADD  CONSTRAINT [DF_TEmployees_ActiveEmp]  
DEFAULT ((0)) FOR [ActiveEmp]
GO
 
ALTER TABLE [dbo].[TEmployees] ADD  CONSTRAINT [DF_TEmployees_InDate]  
DEFAULT (getdate()) FOR [InDate]
GO
 
--INSERT sample data: Employee-- 
INSERT INTO [dbo].[TEmployees] ([Name],[SurName],[DataOfBirth],[Nationality],
[Salary],[DEPTID],[ActiveEmp])
VALUES ('JAG','KIM','2001-01-10','RU',1000.50,10,0)
 
--Select Data: Employee as Json array--
SELECT [name], [SurName] as b FROM [dbo].[TEmployees] FOR JSON AUTO;
SELECT [name], [SurName] as b FROM [dbo].[TEmployees] FOR JSON  PATH, ROOT('Employees');
SELECT [name], [SurName] as b FROM [dbo].[TEmployees] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;




3. Insert Json Object to Database Table


 /**Topic3: Insert Json array to SQL Employees Table**/
DECLARE @EmpJson varchar(max) 
set @EmpJson = '[
 { "theName": "Sam", "SurName": "Joshi","DateOfBirth": "1980-03-25",
 "Nationality": "IND", "Salary":1001.11, "DEPTID":10, "ActiveEmp":1},
 { "theName": "Kin", "SurName": "Tej","DateOfBirth": "1971-01-31",
 "Nationality": "USA", "Salary":2501.11, "DEPTID":2, "ActiveEmp":1}
]'
 
--Select @EmpJson
INSERT INTO [TEmployees] ([Name],[SurName],[DataOfBirth],[Nationality],[Salary],[DEPTID],[ActiveEmp]) 
 SELECT theName, SurName, DateOfBirth, Nationality, Salary, DEPTID, ActiveEmp 
 FROM OPENJSON(@EmpJson)
 WITH (theName nchar(50), SurName nchar(50), DateOfBirth date, Nationality nchar(3), Salary float, DEPTID int, ActiveEmp bit)
 
--SELECT TOP (1000) *   FROM [dbo].[TEmployees]
--delete from [TEmployees]

4. Update Json Object to Database Table
/**Topic4: Update SQL Employees Table using 
Json array data object 
 
**/




DECLARE @EmpJson varchar(max) 
set @EmpJson = '[
 {"EID":2, "theName": "Sam", "SurName": "Joshi","DateOfBirth": "1980-03-25",
 "Nationality": "IND", "Salary":1001.11, "DEPTID":10, "ActiveEmp":1},
 { "EID":17,"theName": "Keli", "SurName": "Tej","DateOfBirth": "1971-01-31",
 "Nationality": "USA", "Salary":2501.11, "DEPTID":2, "ActiveEmp":1}
]'
UPDATE [TEmployees]
 SET [Name] = json.theName,
 [SurName] = json.SurName,
 [DataOfBirth] = json.dateOfBirth,
 [Nationality] = json.Nationality,
 [Salary] = json.salary,
 [DEPTID] = json.deptid,
 [ActiveEmp] = json.ActiveEmp
 FROM OPENJSON(@EmpJson)
 WITH (EID int, theName nchar(50), SurName nchar(50), DateOfBirth date, 
 Nationality nchar(3), Salary float, DEPTID int, ActiveEmp bit) AS json
  WHERE [TEmployees].EMPID = json.eid

4. Conditional UPDATE or INSERT to sql Table Using Json DATA Object
 
/**Topic5: Inser or Update SQL Employees Table using 
Json array data object 
  
**/
 
 

 ---MERGE statement that will insert row if it does not exist in the TEmployees table,
 -- and update it if there is a match
 DECLARE @EmpJson varchar(max) 
set @EmpJson = '[
 {"EID":1, "theName": "Kali", "SurName": "Sala","DateOfBirth": "1980-03-25",
 "Nationality": "IND", "Salary":1001.11, "DEPTID":10, "ActiveEmp":1},
 { "EID":17,"theName": "Jem", "SurName": "Farrero","DateOfBirth": "1971-01-31",
 "Nationality": "USA", "Salary":2501.11, "DEPTID":2, "ActiveEmp":1}
]'
MERGE TEmployees AS P
USING (
    SELECT *
FROM OPENJSON(@EmpJson)
WITH (EID int, theName nchar(50), SurName nchar(50), DateOfBirth date, 
Nationality nchar(3), Salary float, DEPTID int, ActiveEmp bit)) AS json
   ON P.EMPID = json.EID
 
WHEN MATCHED THEN
    UPDATE SET
 P.[Name] = json.theName,
 P.[SurName] = json.SurName,
 P.[DataOfBirth] = json.dateOfBirth,
 P.[Nationality] = json.Nationality,
 P.[Salary] = json.salary,
 P.[DEPTID] = json.deptid,
 P.[ActiveEmp] = json.ActiveEmp
 
WHEN NOT MATCHED THEN
    INSERT ([Name],[SurName],[DataOfBirth],[Nationality],[Salary],[DEPTID],[ActiveEmp]) 
    VALUES (json.theName, json.SurName, json.dateOfBirth, json.Nationality, 
 json.salary,json.deptid,json.ActiveEmp);