SQL FOR JSON Tour

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:

Robot Table Structure.

Robot Table Structure.

House Table Structure.

House Table Structure.

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.