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}}]}
No comments:
Post a Comment