SQL Server 2016 New Feature

SQL Server 2016 New Feature :- JSON

INTRODUCTION

JSON stands for Java Script Object Notation, just like XML, JSON is the syntax for storing and exchanging data. It is more lightweight than XML.

EXAMPLE

[{ "name": "Bob", "skills": ["SQL", "C#", "Azure"] }, { "name": "Lata", "surname": "Mangeshkar" }]

SQL Server provides built-in functions and operators that let you do the following things with JSON text.

• Parse JSON text and read or modify values.
• Transform arrays of JSON objects into table format.
• Run any Transact-SQL query on the converted JSON objects.
• Format the results of Transact-SQL queries in JSON format.
  

Function used in SQL server for JSON manipulation/retrieval

• Use the JSON_VALUE function to extract a scalar value from a JSON string.
• Use JSON_QUERY to extract an object or an array from a JSON string.
• Use the ISJSON function to test whether a string contains valid JSON.
• Use the JSON_MODIFY function to change a value in a JSON string.

SCENARIO

We have department and employee table connected with foreigh key DeptID, We need JSON output as a whole and JSON by department.

SOLUTION

First we create tables and insert data as shown below.

CREATE TABLE DEPARTMENT

(

DEPTID INT IDENTITY(1,1) PRIMARY KEY,

DEPTNAME NVARCHAR(255)

)

INSERT INTO DEPARTMENT VALUES ('DEPT1'),('DEPT2'),('DEPT3')

CREATE TABLE EMPLOYEE

(

EMPID INT IDENTITY(1,1) PRIMARY KEY,

DEPTID INT REFERENCES DEPARTMENT(DEPTID),

EMPNAME NVARCHAR(255),

DOB DATE,

DOJ DATE

)

INSERT INTO EMPLOYEE VALUES (1,'Emp1','1984-01-01','2001-01-01'),(1,'Emp1','1984-01-01','2001-01-01'),(1,'Emp2','1984-05-01','2001-01-15'),(2,'Emp3','1982-01-01','2000-01-01'),(3,'Emp4','1984-12-01','2001-12-01')

For retrieving complete JSON

SELECT 

DEP.DEPTNAME,EMP.EMPID,EMP.EMPNAME,EMP.DOB,EMP.DOJ

FROM DEPARTMENT AS DEP

INNER JOIN EMPLOYEE AS EMP

ON DEP.DEPTID = EMP.DEPTID

FOR JSON PATH 

For retrieving JSON by each Department

SELECT  DEPTID, DEPTNAME, (SELECT EMPID,EMPNAME,DOJ,DOB FROM EMPLOYEE WHERE DEPTID = DEP.DEPTID FOR JSON PATH) AS EmployeeInfo 

FROM DEPARTMENT AS DEP