In SQL Server 2016, Microsoft provides built-in support for importing, exporting, parsing, and querying JSON documents or fragments.
In this article, let us see examples on how to convert relational data (data in rows & columns format) to JSON format
--Sample data DECLARE @MSSQL1 TABLE (ID INT, DBName VARCHAR(30)) INSERT INTO @MSSQL1 SELECT 1,'SQL Server 2012' INSERT INTO @MSSQL1 SELECT 2,'SQL Server 2014' INSERT INTO @MSSQL1 SELECT 3,'SQL Server 2016' DECLARE @MSSQ2 TABLE (ID INT, CodeName VARCHAR(30)) INSERT INTO @MSSQ2 SELECT 1,'Denali' INSERT INTO @MSSQ2 SELECT 2,'Hekaton' INSERT INTO @MSSQ2 (ID) SELECT 3
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMP9G4JXaa4rfZMfV1oKFm01hEETP4JIM5uvofZ6pFxzsuCiVaPgyI1CyfWe_2g5c1H_YwGsZWYhfXyaXJ9-Lx157Qnq-kUu_47BPMgULXgCDaKjQX10d3iwGk7TgVGL_HIJZqrdK9Kjew/s640/j1.png)
FOR JSON AUTO:
FOR JSON AUTO formats the JSON output based on the structure of the SELECT statement.SELECT DBName ,CodeName FROM @MSSQL1 A JOIN @MSSQ2 B ON A.ID = B.ID FOR JSON AUTO
JSON Output:
[ { "DBName":"SQL Server 2012", "B":[{"CodeName":"Denali"}] }, { "DBName":"SQL Server 2014", "B":[{"CodeName":"Hekaton"}] }, { "DBName":"SQL Server 2016", "B":[{}] } ]
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhowbYpTdErtem8y4hjhmt8qafCXP4Nkf_FuxC5xfuveL51Bdv0SZBEFADzu_IkXweXjly-36XlexmsW_XoAMKVCi8k0jItmAek2kuc7VAwK5aFrTX9ep2RyAXuxKsbdEIdvQuEIEIg3sLc/s640/j2.png)
FOR JSON PATH:
FOR JSON PATH gives us full control over the format of the JSON output.SELECT DBName AS "J1.DBName" ,CodeName AS "J2.CodeName" FROM @MSSQL1 A JOIN @MSSQ2 B ON A.ID = B.ID FOR JSON PATH,root('MSSQL')
If we want to add a single, top-level element to the JSON output, We can add root option as shown in above example.
JSON Output:
{"MSSQL": [ {"J1": {"DBName":"SQL Server 2012"}, "J2": {"CodeName":"Denali"}}, {"J1": {"DBName":"SQL Server 2014"}, "J2": {"CodeName":"Hekaton"}}, {"J1":{"DBName":"SQL Server 2016"}} ] }
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpUULJzTPCh4Vg7Qsb7WkVMQEjT6q7AU4C19qYb4Mz93XT3QELQ3Ly4E7n0O672DvQlytGhu2GbJ_QQI5CFB64wpseH4cZItPQsb3e6GlIjKEloEw6HtlwmaCuZxi1YR_6L7ZWa3gCfkV6/s640/j3.png)
Options:- INCLUDE_NULL_VALUES & WITHOUT_ARRAY_WRAPPER
- INCLUDE_NULL_VALUES - To include null values in the JSON output.
- WITHOUT_ARRAY_WRAPPER - To remove the square brackets that surround the JSON output by default.
SELECT DBName AS "J1.DBName" ,CodeName AS "J2.CodeName" FROM @MSSQL1 A JOIN @MSSQ2 B ON A.ID = B.ID FOR JSON PATH,INCLUDE_NULL_VALUES,WITHOUT_ARRAY_WRAPPERJSON Output:
{"J1": {"DBName":"SQL Server 2012"}, "J2":{"CodeName":"Denali"}}, {"J1":{"DBName":"SQL Server 2014"}, "J2":{"CodeName":"Hekaton"}}, {"J1":{"DBName":"SQL Server 2016"}, "J2":{"CodeName":null}}
Scripts are tested in Microsoft Azure VM - SQL Server 2016 CTP 3.2
See Also:
Reference:
No comments:
Post a Comment