John Xiong

Professional Figure-It-Outer & Outdoor Fanatic

Data

T-SQL – JSON Cheatsheet

John

I’ve recently been doing a lot of JSON stuff in T-SQL. I figured it would benefit me to create a cheatsheet of some of the coding I’ve been doing. Hopefully they help you in some way!

A few quick links for more info:

Converting Single Object

In the code below, you’ll find how to format a single object from a result set to JSON and back:

DECLARE @tblSoccerTeam as TABLE (
PersonID INT NOT NULL IDENTITY,
JerseyNumber TINYINT NOT NULL,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
Salary MONEY NOT NULL,
StartDate DATE NOT NULL,
EndDate DATE NULL,
IsTradable BIT NOT NULL
)
INSERT INTO @tblSoccerTeam ( JerseyNumber, FirstName, LastName, Salary, StartDate, EndDate, IsTradable)
VALUES
(7, 'Christiano', 'Ronaldo', 324482190.94, '2010-03-06', NULL, 0)
/*******************************************************/
/************** FORMAT RESULT SET TO JSON **************/
/*******************************************************/
SELECT *
FROM @tblSoccerTeam
FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
/* output:
{
"PersonID": 1,
"JerseyNumber": 7,
"FirstName": "Christiano",
"LastName": "Ronaldo",
"Salary": 324482190.94,
"StartDate": "2010-03-06",
"EndDate": null,
"IsTradable": false
}'
*/
/**************************************************/
/************** FORMAT JSON TO TABLE **************/
/**************************************************/
DECLARE @json varchar(max) =
'{
"PersonID": 1,
"JerseyNumber": 7,
"FirstName": "Christiano",
"LastName": "Ronaldo",
"Salary": 324482190.94,
"StartDate": "2010-03-06",
"EndDate": null,
"IsTradable": false
}'
SELECT *
FROM OPENJSON(@json)
WITH ( PersonID int '$.PersonID',
JerseyNumber int '$.JerseyNumber',
FirstName varchar(20) '$.FirstName',
LastName varchar(20) '$.LastName',
Salary money '$.Salary',
StartDate date '$.StartDate',
EndDate date '$.EndDate',
IsTradable bit '$.IsTradable' )
/* output:
PersonID JerseyNumber FirstName LastName Salary StartDate EndDate IsTradable
1 7 Christiano Ronaldo 324482190.9 2010-03-06 NULL 0
*/
/******************************************************************************/
/************** FORMAT RESULT SET TO JSON & GIVE NAME TO OBJECT **************/
/******************************************************************************/
SELECT JerseyNumber AS [player.jerseyNumber],
FirstName AS [player.firstName],
LastName AS [player.lastName],
Salary AS [player.salary],
StartDate AS [player.startDate],
EndDate AS [player.endDate],
IsTradable AS [player.isTradable]
FROM @tblSoccerTeam
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
/* output:
{
"player": {
"jerseyNumber": 7,
"firstName": "Christiano",
"lastName": "Ronaldo",
"salary": 324482190.94,
"startDate": "2010-03-06",
"isTradable": false
}
}
*/
/**********************************************************************/
/************** FORMAT NAMED SINGLE OBJECT JSON TO TABLE **************/
/**********************************************************************/
SET @json varchar(max) =
'{
"player": {
"jerseyNumber": 7,
"firstName": "Christiano",
"lastName": "Ronaldo",
"salary": 324482190.94,
"startDate": "2010-03-06",
"isTradable": false
}
}'
SELECT jerseyNumber, firstName, lastName, salary, startDate, endDate, isTradable
FROM OPENJSON(@json)
WITH ( player nvarchar(max) AS JSON)
CROSS APPLY OPENJSON(player)
WITH ( jerseyNumber int '$.jerseyNumber',
firstName varchar(20) '$.firstName',
lastName varchar(20) '$.lastName',
salary money '$.salary',
startDate date '$.startDate',
endDate date '$.endDate',
isTradable bit '$.isTradable' )
/* output:
jerseyNumber firstName lastName salary startDate endDate isTradable
7 Christiano Ronaldo 324482190.9 2010-03-06 NULL 0
*/
view raw tsql_json_singleobject.sql hosted with ❤ by GitHub

Converting Array Object

In the code below, you’ll find how to format an array from a result set to JSON and back:

DECLARE @tblSoccerTeam as TABLE (
PersonID INT NOT NULL IDENTITY,
JerseyNumber TINYINT NOT NULL,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
Salary MONEY NOT NULL,
StartDate DATE NOT NULL,
EndDate DATE NULL,
IsTradable BIT NOT NULL
)
INSERT INTO @tblSoccerTeam ( JerseyNumber, FirstName, LastName, Salary, StartDate, EndDate, IsTradable)
VALUES
(7, 'Christiano', 'Ronaldo', 324482190.94, '2010-03-06', NULL, 0),
(10, 'Lionel', 'Messi', 203837263.35, '2015-07-25', NULL, 0),
(1, 'John', 'Xiong', -100.00, '2000-01-01', '2000-01-02', 0)
/*****************************************************************/
/************** CREATE ARRAY OF SINGLE OBJECT TYPE **************/
/*****************************************************************/
/*
FORMAT:
- team
- player
- jerseyNumber
- firstName
- lastName
- salary
- startDate
- endDate
- isTradable
*/
SELECT JerseyNumber AS [player.jerseyNumber],
FirstName AS [player.firstName],
LastName AS [player.lastName],
Salary AS [player.salary],
StartDate AS [player.startDate],
EndDate AS [player.endDate],
IsTradable AS [player.isTradable]
FROM @tblSoccerTeam
FOR JSON PATH, ROOT('team')
/* output:
{
"team": [
{
"player": {
"jerseyNumber": 7,
"firstName": "Christiano",
"lastName": "Ronaldo",
"salary": 324482190.94,
"startDate": "2010-03-06",
"isTradable": false
}
},
{
"player": {
"jerseyNumber": 10,
"firstName": "Lionel",
"lastName": "Messi",
"salary": 203837263.35,
"startDate": "2015-07-25",
"isTradable": false
}
},
{
"player": {
"jerseyNumber": 1,
"firstName": "John",
"lastName": "Xiong",
"salary": -100,
"startDate": "2000-01-01",
"endDate": "2000-01-02",
"isTradable": false
}
}
]
}
*/
/*********************************************************************************/
/************** FORMAT ARRAY OF SINGLE OBJECT TYPE JSON INTO TABLE **************/
/*********************************************************************************/
DECLARE @json varchar(max) =
'{
"team": [
{
"player": {
"jerseyNumber": 7,
"firstName": "Christiano",
"lastName": "Ronaldo",
"salary": 324482190.94,
"startDate": "2010-03-06",
"isTradable": false
}
},
{
"player": {
"jerseyNumber": 10,
"firstName": "Lionel",
"lastName": "Messi",
"salary": 203837263.35,
"startDate": "2015-07-25",
"isTradable": false
}
},
{
"player": {
"jerseyNumber": 1,
"firstName": "John",
"lastName": "Xiong",
"salary": -100,
"startDate": "2000-01-01",
"endDate": "2000-01-02",
"isTradable": false
}
}
]
}'
SELECT jerseyNumber, firstName, lastName, salary, startDate, endDate, isTradable
FROM OPENJSON(@json)
WITH ( team nvarchar(max) AS JSON)
CROSS APPLY OPENJSON(team)
WITH ( player nvarchar(max) AS JSON)
CROSS APPLY OPENJSON(player)
WITH ( jerseyNumber int '$.jerseyNumber',
firstName varchar(20) '$.firstName',
lastName varchar(20) '$.lastName',
salary money '$.salary',
startDate date '$.startDate',
endDate date '$.endDate',
isTradable bit '$.isTradable' )
/* output:
jerseyNumber firstName lastName salary startDate endDate isTradable
7 Christiano Ronaldo 324482190.9 2010-03-06 NULL 0
10 Lionel Messi 203837263.4 2015-07-25 NULL 0
1 John Xiong -100 2000-01-01 2000-01-02 0
*/
/***************************************************************/
/************** CREATE ARRAY OF MULTIPLE OBJECTS **************/
/***************************************************************/
/*
FORMAT:
- team
- players
- player
- jerseyNumber
- firstName
- lastName
- salary
- startDate
- endDate
- isTradable
- managers
- manager
- firstName
- lastName
*/
DECLARE @tblManagement AS TABLE (
PersonID INT NOT NULL IDENTITY,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL
)
INSERT INTO @tblManagement (FirstName, LastName)
VALUES
('Manager', 'One'),
('Manager', 'Two')
SELECT ( SELECT JerseyNumber AS [player.jerseyNumber],
FirstName AS [player.firstName],
LastName AS [player.lastName],
Salary AS [player.salary],
StartDate AS [player.startDate],
EndDate AS [player.endDate],
IsTradable AS [player.isTradable]
FROM @tblSoccerTeam
FOR JSON PATH ) AS players,
( SELECT PersonID AS [manager.personID],
FirstName AS [manager.firstName],
LastName AS [manager.lastName]
FROM @tblManagement
FOR JSON PATH) AS management
FOR JSON PATH, ROOT('team')
/* output:
{
"team": [
{
"players": [
{
"player": {
"jerseyNumber": 7,
"firstName": "Christiano",
"lastName": "Ronaldo",
"salary": 324482190.94,
"startDate": "2010-03-06",
"isTradable": false
}
},
{
"player": {
"jerseyNumber": 10,
"firstName": "Lionel",
"lastName": "Messi",
"salary": 203837263.35,
"startDate": "2015-07-25",
"isTradable": false
}
},
{
"player": {
"jerseyNumber": 1,
"firstName": "John",
"lastName": "Xiong",
"salary": -100,
"startDate": "2000-01-01",
"endDate": "2000-01-02",
"isTradable": false
}
}
],
"management": [
{
"manager": {
"personID": 1,
"firstName": "Manager",
"lastName": "One"
}
},
{
"manager": {
"personID": 2,
"firstName": "Manager",
"lastName": "Two"
}
}
]
}
]
}
*/
view raw tsql_json_multipleobjects.sql hosted with ❤ by GitHub

Happy coding!

Back To Top