SQL Server - Convert relational data to JSON data

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_WRAPPER

JSON 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:

•  My posts on SQL Server JSON

Reference:

•  For Clause
• JSON AUTO
• JSON PATH