I’ve worked on numerous occasions with the various flavours of the FOR XML clause within SQL Server. Taking that a step further, this seemed like as good a time as ever to check out how to work with the FOR JSON clause.
This clause can, simply put, be used to boil down data retrieved via a SELECT statement into formatted JSON, with a custom structure as required – simples. So, how do we go about this?
To illustrate how this all strings together I’ve scripted together a couple of examples that you can lift and test yourself, as desired. Coming up with examples can be the hardest thing sometimes; in an effort to avoid falling into the classic ‘Person’ table example (Car for C# and so on and so forth 😉 !) I’ve gone with two ‘super imaginative’ (well, maybe not; but give me some credit here!) fictitious relational data setups! Firstly, I use the concept of ‘Robot’ and ‘RobotDanceMoves’ for a look at one-to-one relationships (which gets extended a little later one). Next up, I use the idea of ‘Houses’ to representing a more complex example. Houses have ‘Rooms’ which in turn contain ‘RoomObjects’, this gives us some opportunities to explore one-to-many relationships and how to structure the SQL to get the desired JSON output.
Our test table structures look like this:
We’ll start with utilising the Robot and RobotDanceMoves tables and then use the other tables in the last couple of examples to show a more complex, nested setup.
To get us out of the gate, our first piece of SQL uses FOR JSON PATH in its simplest form, we’re enforcing a one-to-one relationship via the INNER JOIN clause by specifying ‘FavouriteMove = 1’.
SQL:
-- Produce a very simple, flat representation of a Robot and their favourite dance move SELECT r.Id , r.[Name] , r.Active , rdm.[Name] AS [FavouriteDanceMove] FROM dbo.Robots r INNER JOIN dbo.RobotDanceMoves rdm ON ( r.Id = rdm.RobotId AND rdm.FavouriteMove = 1 ) FOR JSON PATH;
JSON output:
[
{
"Id": 1,
"Name": "Barry",
"Active": true,
"FavouriteDanceMove": "Moonwalk"
},
{
"Id": 2,
"Name": "Steve",
"Active": false,
"FavouriteDanceMove": "Thunder Clap"
},
{
"Id": 3,
"Name": "Dave",
"Active": true,
"FavouriteDanceMove": "Moonwalk"
},
{
"Id": 4,
"Name": "Zoe",
"Active": true,
"FavouriteDanceMove": "The Robot"
},
{
"Id": 5,
"Name": "Claire",
"Active": true,
"FavouriteDanceMove": "The Robot"
},
{
"Id": 6,
"Name": "Tracey",
"Active": false,
"FavouriteDanceMove": "Moonwalk"
}
]
It is possible to compartmentalise properties further by using column aliases directly to create a key and nested object. Here the ‘Name’ and the ‘Active’ flag for a Robot is nested within ‘RobotCoreDetails’ and the ‘Name’ of a Robots favourite dance move is nested within ‘RobotDanceMove’ as ‘FavouriteDanceMove’.
SQL:
-- Illustration of how the JSON can be manipulated and formatted further by altering column aliases SELECT r.Id AS [Id] , r.[Name] AS [RobotCoreDetails.Name] , r.Active AS [RobotCoreDetails.Active] , rdm.[Name] AS [RobotDanceMove.FavouriteDanceMove] FROM dbo.Robots r INNER JOIN dbo.RobotDanceMoves rdm ON ( r.Id = rdm.RobotId AND rdm.FavouriteMove = 1 ) FOR JSON PATH;
JSON output:
[
{
"Id": 1,
"RobotCoreDetails": {
"Name": "Barry",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "Moonwalk"
}
},
{
"Id": 2,
"RobotCoreDetails": {
"Name": "Steve",
"Active": false
},
"RobotDanceMove": {
"FavouriteDanceMove": "Thunder Clap"
}
},
{
"Id": 3,
"RobotCoreDetails": {
"Name": "Dave",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "Moonwalk"
}
},
{
"Id": 4,
"RobotCoreDetails": {
"Name": "Zoe",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "The Robot"
}
},
{
"Id": 5,
"RobotCoreDetails": {
"Name": "Claire",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "The Robot"
}
},
{
"Id": 6,
"RobotCoreDetails": {
"Name": "Tracey",
"Active": false
},
"RobotDanceMove": {
"FavouriteDanceMove": "Moonwalk"
}
}
]
As per the FOR XML clause, it is possible to apply a ‘ROOT’ clause. Here, this is used as a wrapper to Robot objects.
SQL:
-- Illustration of adding a ROOT clause to wrap the produced JSON content
SELECT
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active]
, rdm.[Name] AS [RobotDanceMove.FavouriteDanceMove]
FROM dbo.Robots r
INNER JOIN dbo.RobotDanceMoves rdm ON
(
r.Id = rdm.RobotId
AND rdm.FavouriteMove = 1
)
FOR JSON PATH, ROOT('Robots');
JSON output:
{
"Robots": [
{
"Id": 1,
"RobotCoreDetails": {
"Name": "Barry",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "Moonwalk"
}
},
{
"Id": 2,
"RobotCoreDetails": {
"Name": "Steve",
"Active": false
},
"RobotDanceMove": {
"FavouriteDanceMove": "Thunder Clap"
}
},
{
"Id": 3,
"RobotCoreDetails": {
"Name": "Dave",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "Moonwalk"
}
},
{
"Id": 4,
"RobotCoreDetails": {
"Name": "Zoe",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "The Robot"
}
},
{
"Id": 5,
"RobotCoreDetails": {
"Name": "Claire",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "The Robot"
}
},
{
"Id": 6,
"RobotCoreDetails": {
"Name": "Tracey",
"Active": false
},
"RobotDanceMove": {
"FavouriteDanceMove": "Moonwalk"
}
}
]
}
In this next example, we are moving forward into a one-to-many relationship by removing ‘FavouriteMove = 1’ from the INNER JOIN (I’ve left the alias to say ‘FavouriteDanceMove’ at the moment, but this gets tidied up in a subsequent example). You may be tempted to structure the SQL in this way when getting started but the outcome, as shown below, results in a duplication of the Robot object – not a singular Robot supporting multiple dance moves.
SQL:
-- Represents the incorrect way to produce correctly nested results, on this occassion
SELECT
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active]
, rdm.[Name] AS [RobotDanceMove.FavouriteDanceMove]
FROM dbo.Robots r
INNER JOIN dbo.RobotDanceMoves rdm ON r.Id = rdm.RobotId
FOR JSON PATH, ROOT('Robots');
JSON output:
{
"Robots": [
{
"Id": 1,
"RobotCoreDetails": {
"Name": "Barry",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "Moonwalk"
}
},
{
"Id": 1,
"RobotCoreDetails": {
"Name": "Barry",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "The Robot"
}
},
{
"Id": 1,
"RobotCoreDetails": {
"Name": "Barry",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "Thunder Clap"
}
},
{
"Id": 2,
"RobotCoreDetails": {
"Name": "Steve",
"Active": false
},
"RobotDanceMove": {
"FavouriteDanceMove": "The Robot"
}
},
{
"Id": 2,
"RobotCoreDetails": {
"Name": "Steve",
"Active": false
},
"RobotDanceMove": {
"FavouriteDanceMove": "Moonwalk"
}
},
{
"Id": 2,
"RobotCoreDetails": {
"Name": "Steve",
"Active": false
},
"RobotDanceMove": {
"FavouriteDanceMove": "Thunder Clap"
}
},
{
"Id": 3,
"RobotCoreDetails": {
"Name": "Dave",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "Moonwalk"
}
},
{
"Id": 3,
"RobotCoreDetails": {
"Name": "Dave",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "The Robot"
}
},
{
"Id": 3,
"RobotCoreDetails": {
"Name": "Dave",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "Thunder Clap"
}
},
{
"Id": 4,
"RobotCoreDetails": {
"Name": "Zoe",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "The Robot"
}
},
{
"Id": 4,
"RobotCoreDetails": {
"Name": "Zoe",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "Moonwalk"
}
},
{
"Id": 4,
"RobotCoreDetails": {
"Name": "Zoe",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "Thunder Clap"
}
},
{
"Id": 5,
"RobotCoreDetails": {
"Name": "Claire",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "Thunder Clap"
}
},
{
"Id": 5,
"RobotCoreDetails": {
"Name": "Claire",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "Moonwalk"
}
},
{
"Id": 5,
"RobotCoreDetails": {
"Name": "Claire",
"Active": true
},
"RobotDanceMove": {
"FavouriteDanceMove": "The Robot"
}
},
{
"Id": 6,
"RobotCoreDetails": {
"Name": "Tracey",
"Active": false
},
"RobotDanceMove": {
"FavouriteDanceMove": "Moonwalk"
}
},
{
"Id": 6,
"RobotCoreDetails": {
"Name": "Tracey",
"Active": false
},
"RobotDanceMove": {
"FavouriteDanceMove": "Thunder Clap"
}
},
{
"Id": 6,
"RobotCoreDetails": {
"Name": "Tracey",
"Active": false
},
"RobotDanceMove": {
"FavouriteDanceMove": "The Robot"
}
}
]
}
This modification correctly provides a single Robot with multiple dance moves. The way to achieve this is by using a subquery SELECT which is directly linked to the outer SELECT (see the ‘WHERE rdm.RobotId = r.Id’ clause). All SELECT statements require a FOR JSON PATH clause to be present. Some additional formatting has been put in place here to output ‘Yep’ or ‘Nope’ to denote if this particular dance move is the Robots favourite.
SQL:
-- Correctly nested results, illustration one
SELECT
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active]
,
(
SELECT
rdm.[Name]
,
-- Use of CASE statement just to show that normal manipulation of data is possible, as expected
CASE
WHEN rdm.FavouriteMove = 1
THEN 'Yep'
ELSE 'Nope'
END AS [FavouriteMove]
FROM dbo.RobotDanceMoves rdm
WHERE rdm.RobotId = r.Id
FOR JSON PATH
) AS [RobotDanceMoves]
FROM dbo.Robots r
FOR JSON PATH, ROOT('Robots');
JSON output:
{
"Robots": [
{
"Id": 1,
"RobotCoreDetails": {
"Name": "Barry",
"Active": true
},
"RobotDanceMoves": [
{
"Name": "Moonwalk",
"FavouriteMove": "Yep"
},
{
"Name": "The Robot",
"FavouriteMove": "Nope"
},
{
"Name": "Thunder Clap",
"FavouriteMove": "Nope"
}
]
},
{
"Id": 2,
"RobotCoreDetails": {
"Name": "Steve",
"Active": false
},
"RobotDanceMoves": [
{
"Name": "The Robot",
"FavouriteMove": "Nope"
},
{
"Name": "Moonwalk",
"FavouriteMove": "Nope"
},
{
"Name": "Thunder Clap",
"FavouriteMove": "Yep"
}
]
},
{
"Id": 3,
"RobotCoreDetails": {
"Name": "Dave",
"Active": true
},
"RobotDanceMoves": [
{
"Name": "Moonwalk",
"FavouriteMove": "Yep"
},
{
"Name": "The Robot",
"FavouriteMove": "Nope"
},
{
"Name": "Thunder Clap",
"FavouriteMove": "Nope"
}
]
},
{
"Id": 4,
"RobotCoreDetails": {
"Name": "Zoe",
"Active": true
},
"RobotDanceMoves": [
{
"Name": "The Robot",
"FavouriteMove": "Yep"
},
{
"Name": "Moonwalk",
"FavouriteMove": "Nope"
},
{
"Name": "Thunder Clap",
"FavouriteMove": "Nope"
}
]
},
{
"Id": 5,
"RobotCoreDetails": {
"Name": "Claire",
"Active": true
},
"RobotDanceMoves": [
{
"Name": "Thunder Clap",
"FavouriteMove": "Nope"
},
{
"Name": "Moonwalk",
"FavouriteMove": "Nope"
},
{
"Name": "The Robot",
"FavouriteMove": "Yep"
}
]
},
{
"Id": 6,
"RobotCoreDetails": {
"Name": "Tracey",
"Active": false
},
"RobotDanceMoves": [
{
"Name": "Moonwalk",
"FavouriteMove": "Yep"
},
{
"Name": "Thunder Clap",
"FavouriteMove": "Nope"
},
{
"Name": "The Robot",
"FavouriteMove": "Nope"
}
]
}
]
}
Finally, here we have altered the dance move output so that it sits inside a ‘Move’ object. It’s really interesting how aliasing name parts, separated by a period, are directly involved in managing how the content is pushed into nested objects.
SQL:
-- Correctly nested results, illustration two (RobotDanceMoves abstracted into 'Move' using aliases)
SELECT
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active]
,
(
SELECT
rdm.[Name] AS [Move.Name]
,
-- Use of CASE statement just to show that normal manipulation of data is possible, as expected
CASE
WHEN rdm.FavouriteMove = 1
THEN 'Yep'
ELSE 'Nope'
END AS [Move.Favourite]
FROM dbo.RobotDanceMoves rdm
WHERE rdm.RobotId = r.Id
FOR JSON PATH
) AS [RobotDanceMoves]
FROM dbo.Robots r
FOR JSON PATH, ROOT('Robots');
JSON output:
{
"Robots": [
{
"Id": 1,
"RobotCoreDetails": {
"Name": "Barry",
"Active": true
},
"RobotDanceMoves": [
{
"Move": {
"Name": "Moonwalk",
"Favourite": "Yep"
}
},
{
"Move": {
"Name": "The Robot",
"Favourite": "Nope"
}
},
{
"Move": {
"Name": "Thunder Clap",
"Favourite": "Nope"
}
}
]
},
{
"Id": 2,
"RobotCoreDetails": {
"Name": "Steve",
"Active": false
},
"RobotDanceMoves": [
{
"Move": {
"Name": "The Robot",
"Favourite": "Nope"
}
},
{
"Move": {
"Name": "Moonwalk",
"Favourite": "Nope"
}
},
{
"Move": {
"Name": "Thunder Clap",
"Favourite": "Yep"
}
}
]
},
{
"Id": 3,
"RobotCoreDetails": {
"Name": "Dave",
"Active": true
},
"RobotDanceMoves": [
{
"Move": {
"Name": "Moonwalk",
"Favourite": "Yep"
}
},
{
"Move": {
"Name": "The Robot",
"Favourite": "Nope"
}
},
{
"Move": {
"Name": "Thunder Clap",
"Favourite": "Nope"
}
}
]
},
{
"Id": 4,
"RobotCoreDetails": {
"Name": "Zoe",
"Active": true
},
"RobotDanceMoves": [
{
"Move": {
"Name": "The Robot",
"Favourite": "Yep"
}
},
{
"Move": {
"Name": "Moonwalk",
"Favourite": "Nope"
}
},
{
"Move": {
"Name": "Thunder Clap",
"Favourite": "Nope"
}
}
]
},
{
"Id": 5,
"RobotCoreDetails": {
"Name": "Claire",
"Active": true
},
"RobotDanceMoves": [
{
"Move": {
"Name": "Thunder Clap",
"Favourite": "Nope"
}
},
{
"Move": {
"Name": "Moonwalk",
"Favourite": "Nope"
}
},
{
"Move": {
"Name": "The Robot",
"Favourite": "Yep"
}
}
]
},
{
"Id": 6,
"RobotCoreDetails": {
"Name": "Tracey",
"Active": false
},
"RobotDanceMoves": [
{
"Move": {
"Name": "Moonwalk",
"Favourite": "Yep"
}
},
{
"Move": {
"Name": "Thunder Clap",
"Favourite": "Nope"
}
},
{
"Move": {
"Name": "The Robot",
"Favourite": "Nope"
}
}
]
}
]
}
In an effort to show a more complex example the following SQL produces nested content illustrating the relationship between ‘Houses’, ‘Rooms’ and ‘RoomObjects’. This uses the same principle of aliasing subqueries and linking inner/outer queries as before.
SQL:
-- Illustration of producing a query that handles one-to-many relationships gracefully
SELECT
h.Id AS [Id]
, h.[Name] AS [Name]
,
(
SELECT
r.Id AS [Id]
, r.[Name] AS [Name]
,
(
SELECT
ro.Id AS [Id]
, ro.[Name] AS [Name]
FROM dbo.RoomObjects ro
WHERE ro.RoomId = r.Id
FOR JSON PATH
) AS [RoomObjects]
FROM dbo.Rooms r
WHERE r.HouseId = h.Id
FOR JSON PATH
) AS [Rooms]
FROM dbo.Houses h
FOR JSON PATH, ROOT('Houses');
JSON output:
{
"Houses": [
{
"Id": 1,
"Name": "House One",
"Rooms": [
{
"Id": 1,
"Name": "Lounge",
"RoomObjects": [
{
"Id": 1,
"Name": "Lamp"
},
{
"Id": 2,
"Name": "Sofa"
}
]
},
{
"Id": 2,
"Name": "Kitchen",
"RoomObjects": [
{
"Id": 3,
"Name": "Knife"
},
{
"Id": 4,
"Name": "Kettle"
}
]
}
]
},
{
"Id": 2,
"Name": "House Two",
"Rooms": [
{
"Id": 3,
"Name": "Lounge Diner",
"RoomObjects": [
{
"Id": 5,
"Name": "Coffee Table"
},
{
"Id": 6,
"Name": "Armchair"
}
]
},
{
"Id": 4,
"Name": "Kitchen Utility",
"RoomObjects": [
{
"Id": 7,
"Name": "Coffee Machine"
},
{
"Id": 8,
"Name": "Microwave"
}
]
}
]
}
]
}
To really improve the nesting of the output it is possible to combine and align column and subquery aliases to get a nice, clean structure.
SQL:
-- Example extended with some more aliases to format the structure further
-- (full nesting of House -> Rooms -> Room -> RoomObjects -> Object)
SELECT
h.Id AS [House.Id]
, h.[Name] AS [House.Name]
,
(
SELECT
r.Id AS [Room.Id]
, r.[Name] AS [Room.Name]
,
(
SELECT
ro.Id AS [Object.Id]
, ro.[Name] AS [Object.Name]
FROM dbo.RoomObjects ro
WHERE ro.RoomId = r.Id
FOR JSON PATH
) AS [Room.RoomObjects]
FROM dbo.Rooms r
WHERE r.HouseId = h.Id
FOR JSON PATH
) AS [House.Rooms]
FROM dbo.Houses h
FOR JSON PATH, ROOT('Houses');
JSON output:
{
"Houses": [
{
"House": {
"Id": 1,
"Name": "House One",
"Rooms": [
{
"Room": {
"Id": 1,
"Name": "Lounge",
"RoomObjects": [
{
"Object": {
"Id": 1,
"Name": "Lamp"
}
},
{
"Object": {
"Id": 2,
"Name": "Sofa"
}
}
]
}
},
{
"Room": {
"Id": 2,
"Name": "Kitchen",
"RoomObjects": [
{
"Object": {
"Id": 3,
"Name": "Knife"
}
},
{
"Object": {
"Id": 4,
"Name": "Kettle"
}
}
]
}
}
]
}
},
{
"House": {
"Id": 2,
"Name": "House Two",
"Rooms": [
{
"Room": {
"Id": 3,
"Name": "Lounge Diner",
"RoomObjects": [
{
"Object": {
"Id": 5,
"Name": "Coffee Table"
}
},
{
"Object": {
"Id": 6,
"Name": "Armchair"
}
}
]
}
},
{
"Room": {
"Id": 4,
"Name": "Kitchen Utility",
"RoomObjects": [
{
"Object": {
"Id": 7,
"Name": "Coffee Machine"
}
},
{
"Object": {
"Id": 8,
"Name": "Microwave"
}
}
]
}
}
]
}
}
]
}
The full code sample is available here (creation of the tables, test data and some example stored procedures are clubbed together for you to play with as you want):
/*
FOR JSON PATH - Full code example
*/
PRINT ('FOR JSON PATH - Full code example' + CHAR(13) + '--------------------------------------------');
-- 1) Create the 'JsonForTestDatabase', if required
IF (DB_ID('JsonForTestDatabase') IS NULL)
BEGIN
PRINT ('Creating the ''JsonForTestDatabase'' sample database.');
CREATE DATABASE JsonForTestDatabase;
END;
GO
-- 2) Explicitly switch the session to 'point' at the JsonForTestDatabase
USE [JsonForTestDatabase]
GO
-- 3) Create the test tables for example purposes (Robot/House tables)
IF(OBJECT_ID('Robots', 'U') IS NULL)
BEGIN
PRINT ('Creating the ''Robots'' table.');
CREATE TABLE dbo.Robots
(
Id BIGINT NOT NULL PRIMARY KEY IDENTITY(1, 1)
, [Name] NVARCHAR(20) NOT NULL
, Active BIT NOT NULL DEFAULT(1)
);
END;
IF(OBJECT_ID('RobotDanceMoves', 'U') IS NULL)
BEGIN
PRINT ('Creating the ''RobotDanceMoves'' table.');
CREATE TABLE dbo.RobotDanceMoves
(
Id BIGINT NOT NULL PRIMARY KEY IDENTITY(1, 1)
, RobotId BIGINT FOREIGN KEY (RobotId) REFERENCES dbo.Robots(Id)
, [Name] NVARCHAR(20) NOT NULL
, FavouriteMove BIT NOT NULL DEFAULT(0)
);
END;
IF(OBJECT_ID('Houses', 'U') IS NULL)
BEGIN
PRINT ('Creating the ''Houses'' table.');
CREATE TABLE dbo.Houses
(
Id BIGINT PRIMARY KEY IDENTITY(1, 1)
, [Name] NVARCHAR(20)
);
END;
IF(OBJECT_ID('Rooms', 'U') IS NULL)
BEGIN
PRINT ('Creating the ''Rooms'' table.');
CREATE TABLE dbo.Rooms
(
Id BIGINT PRIMARY KEY IDENTITY(1, 1)
, HouseId BIGINT FOREIGN KEY (HouseId) REFERENCES dbo.Houses(Id)
, [Name] NVARCHAR(20)
);
END;
IF(OBJECT_ID('RoomObjects', 'U') IS NULL)
BEGIN
PRINT ('Creating the ''RoomObjects'' table.');
CREATE TABLE dbo.RoomObjects
(
Id BIGINT PRIMARY KEY IDENTITY(1, 1)
, RoomId BIGINT FOREIGN KEY (RoomId) REFERENCES dbo.Rooms(Id)
, [Name] NVARCHAR(20)
);
END;
-- 4) Drop/recreate the Utility SPs
IF(OBJECT_ID('GetRobotsWithFavouriteDanceMoveJson', 'P') IS NOT NULL)
BEGIN
PRINT ('Dropping the ''GetRobotsWithFavouriteDanceMoveJson'' stored procedure.');
DROP PROCEDURE dbo.GetRobotsWithFavouriteDanceMoveJson;
END;
PRINT ('Creating the ''GetRobotsWithFavouriteDanceMoveJson'' stored procedure.');
GO
CREATE PROCEDURE dbo.GetRobotsWithFavouriteDanceMoveJson
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
-- Produce a very simple, flat representation of a Robot and their favourite dance move
SELECT
r.Id
, r.[Name]
, r.Active
, rdm.[Name] AS [FavouriteDanceMove]
FROM dbo.Robots r
INNER JOIN dbo.RobotDanceMoves rdm ON
(
r.Id = rdm.RobotId
AND rdm.FavouriteMove = 1
)
FOR JSON PATH;
-- Return execution status of success
RETURN 0;
END;
GO
IF(OBJECT_ID('GetFormattedRobotsWithFavouriteDanceMoveJson', 'P') IS NOT NULL)
BEGIN
PRINT ('Dropping the ''GetFormattedRobotsWithFavouriteDanceMoveJson'' stored procedure.');
DROP PROCEDURE dbo.GetFormattedRobotsWithFavouriteDanceMoveJson;
END;
PRINT ('Creating the ''GetFormattedRobotsWithFavouriteDanceMoveJson'' stored procedure.');
GO
CREATE PROCEDURE dbo.GetFormattedRobotsWithFavouriteDanceMoveJson
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
-- Illustration of how the JSON can be manipulated and formatted further by altering column aliases
SELECT
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active]
, rdm.[Name] AS [RobotDanceMove.FavouriteDanceMove]
FROM dbo.Robots r
INNER JOIN dbo.RobotDanceMoves rdm ON
(
r.Id = rdm.RobotId
AND rdm.FavouriteMove = 1
)
FOR JSON PATH;
-- Return execution status of success
RETURN 0;
END;
GO
IF(OBJECT_ID('GetRobotsWithFavouriteDanceMoveAndRootJson', 'P') IS NOT NULL)
BEGIN
PRINT ('Dropping the ''GetRobotsWithFavouriteDanceMoveAndRootJson'' stored procedure.');
DROP PROCEDURE dbo.GetRobotsWithFavouriteDanceMoveAndRootJson;
END;
PRINT ('Creating the ''GetRobotsWithFavouriteDanceMoveAndRootJson'' stored procedure.');
GO
CREATE PROCEDURE dbo.GetRobotsWithFavouriteDanceMoveAndRootJson
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
-- Illustration of adding a ROOT clause to wrap the produced JSON content
SELECT
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active]
, rdm.[Name] AS [RobotDanceMove.FavouriteDanceMove]
FROM dbo.Robots r
INNER JOIN dbo.RobotDanceMoves rdm ON
(
r.Id = rdm.RobotId
AND rdm.FavouriteMove = 1
)
FOR JSON PATH, ROOT('Robots');
-- Return execution status of success
RETURN 0;
END;
GO
IF(OBJECT_ID('GetFullRobotDetailAsJson', 'P') IS NOT NULL)
BEGIN
PRINT ('Dropping the ''GetFullRobotDetailAsJson'' stored procedure.');
DROP PROCEDURE dbo.GetFullRobotDetailAsJson;
END;
PRINT ('Creating the ''GetFullRobotDetailAsJson'' stored procedure.');
GO
CREATE PROCEDURE dbo.GetFullRobotDetailAsJson
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
/*
-- Represents the incorrect way to produce correctly nested results, on this occassion
SELECT
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active]
, rdm.[Name] AS [RobotDanceMove.FavouriteDanceMove]
FROM dbo.Robots r
INNER JOIN dbo.RobotDanceMoves rdm ON r.Id = rdm.RobotId
FOR JSON PATH, ROOT('Robots');
-- Correctly nested results, illustration one
SELECT
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active]
,
(
SELECT
rdm.[Name]
,
-- Use of CASE statement just to show that normal manipulation of data is possible, as expected
CASE
WHEN rdm.FavouriteMove = 1
THEN 'Yep'
ELSE 'Nope'
END AS [FavouriteMove]
FROM dbo.RobotDanceMoves rdm
WHERE rdm.RobotId = r.Id
FOR JSON PATH
) AS [RobotDanceMoves]
FROM dbo.Robots r
FOR JSON PATH, ROOT('Robots');
*/
-- Correctly nested results, illustration two (RobotDanceMoves abstracted into 'Move' using aliases)
SELECT
r.Id AS [Id]
, r.[Name] AS [RobotCoreDetails.Name]
, r.Active AS [RobotCoreDetails.Active]
,
(
SELECT
rdm.[Name] AS [Move.Name]
,
-- Use of CASE statement just to show that normal manipulation of data is possible, as expected
CASE
WHEN rdm.FavouriteMove = 1
THEN 'Yep'
ELSE 'Nope'
END AS [Move.Favourite]
FROM dbo.RobotDanceMoves rdm
WHERE rdm.RobotId = r.Id
FOR JSON PATH
) AS [RobotDanceMoves]
FROM dbo.Robots r
FOR JSON PATH, ROOT('Robots');
-- Return execution status of success
RETURN 0;
END;
GO
IF(OBJECT_ID('GetHouseDetailAsJson', 'P') IS NOT NULL)
BEGIN
PRINT ('Dropping the ''GetHouseDetailAsJson'' stored procedure.');
DROP PROCEDURE dbo.GetHouseDetailAsJson;
END;
PRINT ('Creating the ''GetHouseDetailAsJson'' stored procedure.');
GO
CREATE PROCEDURE dbo.GetHouseDetailAsJson
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
/*
-- Illustration of producing a query that handles one-to-many relationships gracefully
SELECT
h.Id AS [Id]
, h.[Name] AS [Name]
,
(
SELECT
r.Id AS [Id]
, r.[Name] AS [Name]
,
(
SELECT
ro.Id AS [Id]
, ro.[Name] AS [Name]
FROM dbo.RoomObjects ro
WHERE ro.RoomId = r.Id
FOR JSON PATH
) AS [RoomObjects]
FROM dbo.Rooms r
WHERE r.HouseId = h.Id
FOR JSON PATH
) AS [Rooms]
FROM dbo.Houses h
FOR JSON PATH, ROOT('Houses');
*/
-- Example extended with some more aliases to format the structure further
-- (full nesting of House -> Rooms -> Room -> RoomObjects -> Object)
SELECT
h.Id AS [House.Id]
, h.[Name] AS [House.Name]
,
(
SELECT
r.Id AS [Room.Id]
, r.[Name] AS [Room.Name]
,
(
SELECT
ro.Id AS [Object.Id]
, ro.[Name] AS [Object.Name]
FROM dbo.RoomObjects ro
WHERE ro.RoomId = r.Id
FOR JSON PATH
) AS [Room.RoomObjects]
FROM dbo.Rooms r
WHERE r.HouseId = h.Id
FOR JSON PATH
) AS [House.Rooms]
FROM dbo.Houses h
FOR JSON PATH, ROOT('Houses');
-- Return execution status of success
RETURN 0;
END;
GO
-- 5) Insert sample data into the test tables...part 1, robots...
IF NOT EXISTS
(
SELECT 1
FROM dbo.Robots
)
BEGIN
PRINT ('Adding dbo.Robot sample data.');
INSERT INTO dbo.Robots
(
[Name]
, Active
)
VALUES
('Barry', 1)
, ('Steve', 0)
, ('Dave', 1)
, ('Zoe', 1)
, ('Claire', 1)
, ('Tracey', 0);
END;
IF NOT EXISTS
(
SELECT 1
FROM dbo.RobotDanceMoves
)
BEGIN
PRINT ('Adding dbo.RobotDanceMoves sample data.');
--If RobotDanceMoves have been inserted yet the produce a mock sample of randomised data where Robots receive
--all three dance moves but a 'favourite' on the fly
INSERT INTO dbo.RobotDanceMoves
(
RobotId
, [Name]
, FavouriteMove
)
SELECT
r.Id
, rm.DanceMove
, rm.FavouriteMove
FROM dbo.Robots r
CROSS APPLY
(
SELECT
TOP 3
am.DanceMove
,
CASE
WHEN ROW_NUMBER() OVER (ORDER BY NEWID()) = 3
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS [FavouriteMove]
FROM
(
SELECT
'Moonwalk' AS [DanceMove]
UNION
SELECT
'Thunder Clap'
UNION
SELECT
'The Robot'
) AS am
WHERE r.Id = r.Id
ORDER BY NEWID()
) AS rm;
END;
--...Part two, houses (very fixed, just as an illustration)
IF NOT EXISTS
(
SELECT 1
FROM dbo.Houses
)
BEGIN
PRINT ('Adding dbo.Houses sample data.');
INSERT INTO dbo.Houses
(
[Name]
)
VALUES
('House One')
, ('House Two');
END;
IF NOT EXISTS
(
SELECT 1
FROM dbo.Rooms
)
BEGIN
PRINT ('Adding dbo.Rooms sample data.');
INSERT INTO dbo.Rooms
(
HouseId
, [Name]
)
VALUES
(1, 'Lounge')
, (1, 'Kitchen')
, (2, 'Lounge Diner')
, (2, 'Kitchen Utility');
END;
IF NOT EXISTS
(
SELECT 1
FROM dbo.RoomObjects
)
BEGIN
PRINT ('Adding dbo.RoomObjects sample data.');
INSERT INTO dbo.RoomObjects
(
RoomId
, [Name]
)
VALUES
(1, 'Lamp')
, (1, 'Sofa')
, (2, 'Knife')
, (2, 'Kettle')
, (3, 'Coffee Table')
, (3, 'Armchair')
, (4, 'Coffee Machine')
, (4, 'Microwave');
END;
GO
--6) Execute all of the sample stored procedures
PRINT (CHAR(13) + 'Processing complete...running example stored procedures...');
EXEC dbo.GetRobotsWithFavouriteDanceMoveJson;
EXEC dbo.GetFormattedRobotsWithFavouriteDanceMoveJson;
EXEC dbo.GetRobotsWithFavouriteDanceMoveAndRootJson;
EXEC dbo.GetFullRobotDetailAsJson;
EXEC dbo.GetHouseDetailAsJson;
I hope there are some valuable and useful ideas in this post and, until the next time, happy coding.
bearandhammer 