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}}]}