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 TableJson array data object**/
4. Conditional UPDATE or INSERT to sql Table Using Json DATA ObjectJson 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);
