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

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":[{}] } ]

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

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