Latest Post

Tuesday, 25 July 2017

FOR JSON Clause in sql server 2016

FOR JSON Clause:

We can be used FOR JSON Clause, if we want our query result (rows) as JSON result. It converts the query result to JSON format.

JSON functions in SQL Server provide you to analyse and query JSON data, transform JSON to relational format, and export SQL query results as JSON text

FOR JSON Clause have two types of variants that is following

·         FOR JSON AUTO
·         FOR JSON PATH

Following is the demo table "Employee" where will apply operation to understand FOR JSON feature in sql 2016.

Employee Table:-

EMPNO
ENAME
JOB
SAL
1
JOHNSON
ADMIN
18000
2
HARDING
MANAGER
52000
3
TAFT
SALES I
25000
4
HOOVER
SALES I
27000
5
LINCOLN
TECH
22500
6
GARFIELD
MANAGER
54000
7
POLK
TECH
25000
8
GRANT
ENGINEER
32000
9
JACKSON
CEO
75000
10
FILLMORE
MANAGER
56000
11
ADAMS
ENGINEER
34000
12
WASHINGTON
ADMIN
18000
13
MONROE
ENGINEER
30000
14
ROOSEVELT
CPA
35000


Query 1: Simple FOR JSON PATH example

As above describe that FOR JSON has two variant, so here is the basic FOR JSON PATH query example

SELECT 'ALOK' ENAME, 'ADMIN' JOB
FOR JSON PATH


Result:

[{"ENAME":"ALOK","JOB":"ADMIN"}]



Query 2: Simple FOR JSON PATH example

FOR JSON AUTO requires at-least one table for generating the JSON, so here is the basic FOR JSON AUTO query example

SELECT EMPNO, ENAME, JOB, SAL from EMP
FOR JSON AUTO

Result:

[{"EMPNO":1,"ENAME":"JOHNSON","JOB":"ADMIN","SAL":18000.00},{"EMPNO":2,"ENAME":"HARDING","JOB":"MANAGER","SAL":52000.00},{"EMPNO":3,"ENAME":"TAFT","JOB":"SALES I","SAL":25000.00},{"EMPNO":4,"ENAME":"HOOVER","JOB":"SALES I","SAL":27000.00},{"EMPNO":5,"ENAME":"LINCOLN","JOB":"TECH","SAL":22500.00},{"EMPNO":6,"ENAME":"GARFIELD","JOB":"MANAGER","SAL":54000.00},{"EMPNO":7,"ENAME":"POLK","JOB":"TECH","SAL":25000.00},{"EMPNO":8,"ENAME":"GRANT","JOB":"ENGINEER","SAL":32000.00},{"EMPNO":9,"ENAME":"JACKSON","JOB":"CEO","SAL":75000.00},{"EMPNO":10,"ENAME":"FILLMORE","JOB":"MANAGER","SAL":56000.00},{"EMPNO":11,"ENAME":"ADAMS","JOB":"ENGINEER","SAL":34000.00},{"EMPNO":12,"ENAME":"WASHINGTON","JOB":"ADMIN","SAL":18000.00},{"EMPNO":13,"ENAME":"MONROE","JOB":"ENGINEER","SAL":30000.00},{"EMPNO":14,"ENAME":"ROOSEVELT","JOB":"CPA","SAL":35000.00}]


If we apply FOR JSON AUTO query without any table, this will error. Below are the queries

SELECT 'ALOK' ENAME, 'ADMIN' BOJ
FOR JSON AUTO

Error:-

Msg 13600, Level 16, State 1, Line 2
FOR JSON AUTO requires at least one table for generating JSON objects. Use FOR JSON PATH or add a FROM clause with a table name.

NOTE: - FOR JSON AUTO requires at least one table for generating JSON objects.
Query 3: FOR JSON PATH/AUTO example where columns in the JSON text output are specified in the SELECT statement



------------ FOR JSON PATH ----------------
SELECT EMPNO, ENAME, JOB, SAL FROM EMP
FOR JSON PATH

------------ FOR JSON AUTO ----------------

SELECT EMPNO, ENAME, JOB, SAL FROM EMP
FOR JSON AUTO


Result:

------------ FOR JSON PATH RESULT----------------

[{"EMPNO":1,"ENAME":"JOHNSON","JOB":"ADMIN","SAL":18000.00},{"EMPNO":2,"ENAME":"HARDING","JOB":"MANAGER","SAL":52000.00},{"EMPNO":3,"ENAME":"TAFT","JOB":"SALES I","SAL":25000.00},{"EMPNO":4,"ENAME":"HOOVER","JOB":"SALES I","SAL":27000.00},{"EMPNO":5,"ENAME":"LINCOLN","JOB":"TECH","SAL":22500.00},{"EMPNO":6,"ENAME":"GARFIELD","JOB":"MANAGER","SAL":54000.00},{"EMPNO":7,"ENAME":"POLK","JOB":"TECH","SAL":25000.00},{"EMPNO":8,"ENAME":"GRANT","JOB":"ENGINEER","SAL":32000.00},{"EMPNO":9,"ENAME":"JACKSON","JOB":"CEO","SAL":75000.00},{"EMPNO":10,"ENAME":"FILLMORE","JOB":"MANAGER","SAL":56000.00},{"EMPNO":11,"ENAME":"ADAMS","JOB":"ENGINEER","SAL":34000.00},{"EMPNO":12,"ENAME":"WASHINGTON","JOB":"ADMIN","SAL":18000.00},{"EMPNO":13,"ENAME":"MONROE","JOB":"ENGINEER","SAL":30000.00},{"EMPNO":14,"ENAME":"ROOSEVELT","JOB":"CPA","SAL":35000.00}]

------------ FOR JSON AUTO RESULT----------------

[{"EMPNO":1,"ENAME":"JOHNSON","JOB":"ADMIN","SAL":18000.00},{"EMPNO":2,"ENAME":"HARDING","JOB":"MANAGER","SAL":52000.00},{"EMPNO":3,"ENAME":"TAFT","JOB":"SALES I","SAL":25000.00},{"EMPNO":4,"ENAME":"HOOVER","JOB":"SALES I","SAL":27000.00},{"EMPNO":5,"ENAME":"LINCOLN","JOB":"TECH","SAL":22500.00},{"EMPNO":6,"ENAME":"GARFIELD","JOB":"MANAGER","SAL":54000.00},{"EMPNO":7,"ENAME":"POLK","JOB":"TECH","SAL":25000.00},{"EMPNO":8,"ENAME":"GRANT","JOB":"ENGINEER","SAL":32000.00},{"EMPNO":9,"ENAME":"JACKSON","JOB":"CEO","SAL":75000.00},{"EMPNO":10,"ENAME":"FILLMORE","JOB":"MANAGER","SAL":56000.00},{"EMPNO":11,"ENAME":"ADAMS","JOB":"ENGINEER","SAL":34000.00},{"EMPNO":12,"ENAME":"WASHINGTON","JOB":"ADMIN","SAL":18000.00},{"EMPNO":13,"ENAME":"MONROE","JOB":"ENGINEER","SAL":30000.00},{"EMPNO":14,"ENAME":"ROOSEVELT","JOB":"CPA","SAL":35000.00}]

Query 4: You need to define the property INCLUDE_NULL_VALUES in FOR JSON clause to include NULL values in the JSON output.

------------ FOR JSON PATH RESULT----------------
SELECT EMPNO, ENAME, JOB, SAL FROM EMP
FOR JSON PATH,
INCLUDE_NULL_VALUES

------------ FOR JSON AUTO RESULT----------------

SELECT EMPNO, ENAME, JOB, SAL FROM EMP
FOR JSON AUTO,
INCLUDE_NULL_VALUES


Result:

------------ FOR JSON PATH RESULT----------------

[{"EMPNO":1,"ENAME":"Alok","JOB":"ADMIN","SAL":18000.00}, {"EMPNO":2,"ENAME":"Anil","JOB":"MANAGER","SAL":null}]

------------ FOR JSON AUTO RESULT----------------

[{"EMPNO":1,"ENAME":"Alok","JOB":"ADMIN","SAL":18000.00}, {"EMPNO":2,"ENAME":"Anil","JOB":"MANAGER","SAL":null}]



Query 4: ROOT option to generate a wrapper object around the generated JSON output in the FOR JSON clause.

------------ FOR JSON PATH RESULT----------------
SELECT EMPNO, ENAME, JOB, SAL FROM EMP
FOR JSON PATH,
INCLUDE_NULL_VALUES,
ROOT ('Employees')

------------ FOR JSON AUTO RESULT----------------

SELECT EMPNO, ENAME, JOB, SAL FROM EMP
FOR JSON AUTO,
INCLUDE_NULL_VALUES,
ROOT ('Employees')


Result:

------------ FOR JSON PATH RESULT----------------

{"Employees":[{"EMPNO":1,"ENAME":"Alok","JOB":"ADMIN","SAL":18000.00},
{"EMPNO":2,"ENAME":"Anil","JOB":"MANAGER","SAL":null}]}

------------ FOR JSON AUTO RESULT----------------

{"Employees":[{"EMPNO":1,"ENAME":"Alok","JOB":"ADMIN","SAL":18000.00},
{"EMPNO":2,"ENAME":"Anil","JOB":"MANAGER","SAL":null}]}


Query 5:  By using “.” Symbol, we can name the each object in the resultant JSON and also we can convert each row into a JSON object with multiple sub-objects by using “.”

SELECT EMPNO [Profile.Id], ENAME [Profile.Name],
 JOB [JobProfile.JOB], SAL [JobProfile.SAL],DEPT [JobProfile.DEPT]
FROM dbo.EMP FOR JSON PATH, ROOT('Employee')

Result:


{"Employee":[{"Profile":{"Id":1,"Name":"Alok"},"JobProfile":{"JOB":"ADMIN","SAL":18000.00,"DEPT":4}},{"Profile":{"Id":2,"Name":"Anil"},"JobProfile":{"JOB":"MANAGER","DEPT":3}}]}

Government Jobs