JSON – Part 1: FOR JSON

JSON support was introduced in SQL Server 2016 and is part of the 70-761 – Querying Data with Transact-SQL exam.

Some of it works in a similar way to the XML functionality that has been around for a long time, for example, there is a FOR JSON clause that returns data in JSON format in a similar way FOR XML returns data in XML format. There are other functions available for reading and processing JSON data and I’ll cover some of those in future posts as part of my prep for exam 70-761.

Full details of JSON in SQL Server can be found here.

FOR JSON

Let’s create a table containing a few different data types and see how the FOR JSON clause works.

DROP TABLE IF EXISTS dbo.ForJsonTest1;
GO

CREATE TABLE dbo.ForJsonTest1
(
    IntColumn INT NOT NULL,
    BitColumn BIT NOT NULL,
    DecColumn DECIMAL(5,2) NOT NULL,
    FixedCharColumn CHAR(5) NOT NULL,
    VariableCharColumn VARCHAR(10) NOT NULL,
    DateColumn DATE NOT NULL,
    DateTimeColumn DATETIME2(7) NOT NULL,
    BinaryColumn VARBINARY(10) NOT NULL,
    MoneyColumn MONEY NOT NULL,
    UniqueIDColumn UNIQUEIDENTIFIER NOT NULL,
    NullableColumn VARCHAR(10) NULL
);
GO

INSERT INTO dbo.ForJsonTest1
(
    IntColumn,
    BitColumn,
    DecColumn,
    FixedCharColumn,
    VariableCharColumn,
    DateColumn,
    DateTimeColumn,
    BinaryColumn,
    MoneyColumn,
    UniqueIDColumn,
    NullableColumn
)
VALUES
(
    100,
    1,
    12.34,
    'AB',
    'ABCD',
    '20180301',
    '20180302 15:12',
    123,
    12.34,
    NEWID(),
    NULL
),
(
    250,
    0,
    54.21,
    'QWERT',
    'QWERTY',
    '20180302',
    '20180303 11:12',
    123,
    12.34,
    NEWID(),
    'NotNull'
);
GO

SELECT *
FROM dbo.ForJsonTest1;
GO

Here what the inserted data looks like in the table

JSON1

AUTO

We can now add the FOR JSON clause which has two different options. The simplest one is AUTO which works as follows…

SELECT *
FROM dbo.ForJsonTest1
FOR JSON AUTO
GO

This returns the JSON in a link as follows…

JSON2

… and when formatted it looks like this…

[
 {
 "IntColumn": 100,
 "BitColumn": true,
 "DecColumn": 12.34,
 "FixedCharColumn": "AB ",
 "VariableCharColumn": "ABCD",
 "DateColumn": "2018-03-01",
 "DateTimeColumn": "2018-03-02T15:12:00",
 "BinaryColumn": "AAAAew==",
 "MoneyColumn": 12.34,
 "UniqueIDColumn": "7A53DDE9-C97F-4384-AB1B-D23BA5AB3ED5"
 },
 {
 "IntColumn": 250,
 "BitColumn": false,
 "DecColumn": 54.21,
 "FixedCharColumn": "QWERT",
 "VariableCharColumn": "QWERTY",
 "DateColumn": "2018-03-02",
 "DateTimeColumn": "2018-03-03T11:12:00",
 "BinaryColumn": "AAAAew==",
 "MoneyColumn": 12.34,
 "UniqueIDColumn": "F36DBD9F-9B01-4C15-B562-BB0B9DF6A689",
 "NullableColumn": "NotNull"
 }
]

Certain SQL Server data types get converted to certain JSON data types and details of each data type can be found here.

We can add a root to the JSON similar to how the XML root works…

SELECT *
FROM dbo.ForJsonTest1
FOR JSON AUTO, ROOT('JSONTesting');
GO
{
 "JSONTesting": [
 {
 "IntColumn": 100,
 "BitColumn": true,
 "DecColumn": 12.34,
 "FixedCharColumn": "AB ",
 "VariableCharColumn": "ABCD",
 "DateColumn": "2018-03-01",
 "DateTimeColumn": "2018-03-02T15:12:00",
 "BinaryColumn": "AAAAew==",
 "MoneyColumn": 12.34,
 "UniqueIDColumn": "7A53DDE9-C97F-4384-AB1B-D23BA5AB3ED5"
 },
 {
 "IntColumn": 250,
 "BitColumn": false,
 "DecColumn": 54.21,
 "FixedCharColumn": "QWERT",
 "VariableCharColumn": "QWERTY",
 "DateColumn": "2018-03-02",
 "DateTimeColumn": "2018-03-03T11:12:00",
 "BinaryColumn": "AAAAew==",
 "MoneyColumn": 12.34,
 "UniqueIDColumn": "F36DBD9F-9B01-4C15-B562-BB0B9DF6A689",
 "NullableColumn": "NotNull"
 }
 ]
}

We can include null values as follows so the NullableColumn is always returned…

SELECT *
FROM dbo.ForJsonTest1
FOR JSON AUTO, INCLUDE_NULL_VALUES;
GO
[
 {
 "IntColumn": 100,
 "BitColumn": true,
 "DecColumn": 12.34,
 "FixedCharColumn": "AB ",
 "VariableCharColumn": "ABCD",
 "DateColumn": "2018-03-01",
 "DateTimeColumn": "2018-03-02T15:12:00",
 "BinaryColumn": "AAAAew==",
 "MoneyColumn": 12.34,
 "UniqueIDColumn": "7A53DDE9-C97F-4384-AB1B-D23BA5AB3ED5",
 "NullableColumn": null
 },
 {
 "IntColumn": 250,
 "BitColumn": false,
 "DecColumn": 54.21,
 "FixedCharColumn": "QWERT",
 "VariableCharColumn": "QWERTY",
 "DateColumn": "2018-03-02",
 "DateTimeColumn": "2018-03-03T11:12:00",
 "BinaryColumn": "AAAAew==",
 "MoneyColumn": 12.34,
 "UniqueIDColumn": "F36DBD9F-9B01-4C15-B562-BB0B9DF6A689",
 "NullableColumn": "NotNull"
 }
]

We can use the FOR JSON clause when joining tables together and we get nested JSON as follows…

DROP TABLE IF EXISTS dbo.Joining;
GO

CREATE TABLE dbo.Joining
(
    IntColumn INT NOT NULL,
    SomeText VARCHAR(100) NOT NULL
);
GO

INSERT INTO dbo.Joining (IntColumn, SomeText)
VALUES
(100, 'AAAAA'),
(100, 'BBBBB'),
(100, 'CCCCC');
GO
SELECT
 F.IntColumn,
 F.BitColumn,
 F.DecColumn,
 F.FixedCharColumn,
 F.VariableCharColumn,
 F.DateColumn,
 F.DateTimeColumn,
 F.BinaryColumn,
 F.MoneyColumn,
 F.UniqueIDColumn,
 F.NullableColumn,
 J.SomeText
FROM dbo.ForJsonTest1 F
LEFT JOIN dbo.Joining J ON J.IntColumn = F.IntColumn
FOR JSON AUTO;
GO

[
 {
 "IntColumn": 100,
 "BitColumn": true,
 "DecColumn": 12.34,
 "FixedCharColumn": "AB ",
 "VariableCharColumn": "ABCD",
 "DateColumn": "2018-03-01",
 "DateTimeColumn": "2018-03-02T15:12:00",
 "BinaryColumn": "AAAAew==",
 "MoneyColumn": 12.34,
 "UniqueIDColumn": "7A53DDE9-C97F-4384-AB1B-D23BA5AB3ED5",
 "J": [
   {"SomeText": "AAAAA"},
   {"SomeText": "BBBBB"},
   {"SomeText": "CCCCC"}
 ]
 },
 {
 "IntColumn": 250,
 "BitColumn": false,
 "DecColumn": 54.21,
 "FixedCharColumn": "QWERT",
 "VariableCharColumn": "QWERTY",
 "DateColumn": "2018-03-02",
 "DateTimeColumn": "2018-03-03T11:12:00",
 "BinaryColumn": "AAAAew==",
 "MoneyColumn": 12.34,
 "UniqueIDColumn": "F36DBD9F-9B01-4C15-B562-BB0B9DF6A689",
 "NullableColumn": "NotNull",
 "J": [
   {}
 ]
 }
]

PATH

We get much more control using PATH instead of AUTO and can easily nest JSON using aliases separated with dots as follows.

SELECT
   IntColumn AS 'Numbers.IntColumn',
   BitColumn AS 'Numbers.BitColumn',
   DecColumn AS 'Numbers.DecColumn',
   MoneyColumn AS 'Numbers.MoneyColumn',
   FixedCharColumn AS 'Strings.FixedCharColumn',
   VariableCharColumn AS 'Strings.VariableCharColumn',
   NullableColumn AS 'Strings.NullableColumn',
   DateColumn AS 'Dates.DateColumn',
   DateTimeColumn AS 'Dates.DateTimeColumn',
   BinaryColumn AS 'Boolean.BinaryColumn',
   UniqueIDColumn AS 'Others.UniqueIDColumn'
FROM dbo.ForJsonTest1
FOR JSON PATH;
GO

[
 {
 "Numbers": {
   "IntColumn": 100,
   "BitColumn": true,
   "DecColumn": 12.34,
   "MoneyColumn": 12.34
 },
 "Strings": {
   "FixedCharColumn": "AB ",
   "VariableCharColumn": "ABCD"
 },
 "Dates": {
   "DateColumn": "2018-03-01",
   "DateTimeColumn": "2018-03-02T15:12:00"
 },
 "Boolean": {
   "BinaryColumn": "AAAAew=="
 },
 "Others": {
   "UniqueIDColumn": "7A53DDE9-C97F-4384-AB1B-D23BA5AB3ED5"
 }
 },
 {
 "Numbers": {
   "IntColumn": 250,
   "BitColumn": false,
   "DecColumn": 54.21,
   "MoneyColumn": 12.34
 },
 "Strings": {
   "FixedCharColumn": "QWERT",
   "VariableCharColumn": "QWERTY",
   "NullableColumn": "NotNull"
 },
 "Dates": {
   "DateColumn": "2018-03-02",
   "DateTimeColumn": "2018-03-03T11:12:00"
 },
 "Boolean": {
   "BinaryColumn": "AAAAew=="
 },
 "Others": {
   "UniqueIDColumn": "F36DBD9F-9B01-4C15-B562-BB0B9DF6A689"
 }
 }
]

Please note, you need to make sure the columns inside nested JSON are all next to each other in the SELECT statement. The following generates the following error because all the Numbers columns and Strings columns are not together…

SELECT
 IntColumn AS 'Numbers.IntColumn',
 BitColumn AS 'Numbers.BitColumn',
 DecColumn AS 'Numbers.DecColumn',
 FixedCharColumn AS 'Strings.FixedCharColumn',
 VariableCharColumn AS 'Strings.VariableCharColumn',
 DateColumn AS 'Dates.DateColumn',
 DateTimeColumn AS 'Dates.DateTimeColumn',
 BinaryColumn AS 'Boolean.BinaryColumn',
 MoneyColumn AS 'Numbers.MoneyColumn',
 UniqueIDColumn AS 'Others.UniqueIDColumn',
 NullableColumn AS 'Strings.NullableColumn'
FROM dbo.ForJsonTest1
FOR JSON PATH;
GO

Msg 13601, Level 16, State 1, Line 77
Property 'Numbers.MoneyColumn' cannot be generated in JSON output due to a conflict with another column name or alias. Use different names and aliases for each column in SELECT list.
Advertisements

4 thoughts on “JSON – Part 1: FOR JSON

  1. Pingback: JSON Support in SQL Server 2016 | Simon Learning SQL Server
  2. Pingback: Exam prep for 70-761 | Simon Learning SQL Server
  3. Pingback: JSON – Part 2: Built in Functions | Simon Learning SQL Server
  4. Pingback: JSON – Part 3: OPENJSON | Simon Learning SQL Server

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