JSON – Part 3: OPENJSON

In previous posts I demonstrated how to use the FOR JSON clause and how some of the built in JSON scalar functions work. In this final post in this series I’m going to demonstrate how the OPENJSON function works.

There are two ways we can use the OPENJSON function. The first is in discovery mode where the schema of the JSON we are dealing with is unknown. The second is to shred data into usable sets when we know the schema.

Firstly, I’m going to demonstrate how to use it in discovery mode to work out what data is in a supplied piece of JSON.

In this example the JSON is short and formatted so it’s easy to read so we know what’s in it just by looking at it, but when that’s not the case the OPENJSON function can be very useful to see what properties exist within a particular JSON object.

The following JSON has data of a few different types and we can just pass it straight into OPENJSON to return some useful details. The OPENJSON function is a table valued function and always returns a data set. For example…

DECLARE @json NVARCHAR(MAX) =
'{
  "CharacterID" : 12,
  "CharacterName" : "Egwene Al''Vere",
  "IsFemale" : true,
  "Address" : {
    "AddressLine1" : "Emonds Field",
    "AddressLine2" : "The Two Rivers"
  },
  "Friends" : ["Rand Al''Thor", "Perrin Aybara", "Matrim Cauthon"],
  "Children" : null
}';

SELECT *
FROM OPENJSON(@json);

JSON3 1

… we can see details about each key-value pair in the JSON object along with a number indicating the type.

The type value corresponds to the following:

Value of the Type column JSON data type
0 null
1 string
2 int
3 true/false
4 array
5 object

That example used a single JSON object but if our JSON is an array of JSON objects then the OPENJSON function will shred them into individual rows and the key column contains the ordinal position of the JSON object in the array as follows…

DECLARE @json_array NVARCHAR(MAX) = 
'[
  {
    "CharacterID" : 12,
    "CharacterName" : "Egwene Al''Vere",
    "IsFemale" : true,
    "Address" : {
      "AddressLine1" : "Emonds Field",
      "AddressLine2" : "The Two Rivers"
    },
    "Friends" : ["Rand Al''Thor", "Perrin Aybara", "Matrim Cauthon"],
    "Children" : null
  },
  {
    "CharacterID" : 13,
    "ChacterName" : "Perrin Aybara",
    "IsFemale" : false,
    "Address" : {
      "AddressLine1" : "Emonds Field",
      "AddressLine2" : "The Two Rivers"
    },
    "Friends" : ["Rand Al''Thor", "Egwene Al''Vere", "Matrim Cauthon", "Nynaeve Al''Merea"],
    "Children" : null
  }
]';

SELECT *
FROM OPENJSON(@json_array);

JSON3 2

If we want to access a single value from an array we can use the second optional parameter to drill down straight to that value as follows…

SELECT *
FROM OPENJSON(@json_array, '$[1]');

JSON3 3

This returns the shredded data from the second JSON object in the array (ordinal positions are zero based).

If the second parameter (the path to start shredding) is not supplied then the entire object specified in the first variable is used. This is the same a specifying “$” as the path. The following two queries are the same…

SELECT *
FROM OPENJSON(@json_array);

SELECT *
FROM OPENJSON(@json_array, '$');

JSON3 4

That covers a few examples of using OPENJSON in discovery mode and now I’m going to show a few examples of how to use it if you know a bit about the schema of the JSON supplied.

We’ve used OPENJSON to discover the schema of our @json_array object so now we can use this and some of the available scalar functions to shred our object into one or more tables.

In this example we’ve got an array and we want to shred that data into individual rows in a data set linked to a header data set containing the rest of the data. One way to do this is as follows…

DECLARE @json NVARCHAR(MAX) =
'{
  "CharacterID" : 12,
  "CharacterName" : "Egwene Al''Vere",
  "IsFemale" : true,
  "Address" : {
    "AddressLine1" : "Emonds Field",
    "AddressLine2" : "The Two Rivers"
  },
  "Friends" : ["Rand Al''Thor", "Perrin Aybara", "Matrim Cauthon"],
  "Children" : null
}';

SELECT
 JSON_VALUE(@json, '$.CharacterID') AS CharacterID,
 JSON_VALUE(@json, '$.CharacterName') AS CharacterName,
 JSON_VALUE(@json, '$.IsFemale') AS IsFemale,
 JSON_VALUE(@json, '$.Address.AddressLine1') AS AddressLine1,
 JSON_VALUE(@json, '$.Address.AddressLine2') AS AddressLine2,
 JSON_VALUE(@json, '$.Children') AS Children

SELECT
  JSON_VALUE(@json, '$.CharacterID') AS CharacterID,
  OJ.value AS Friend
FROM OPENJSON(@json, '$.Friends') OJ;

JSON3 5

Here’s another example where the array is an array of JSON objects rather than individual values…

DECLARE @json_friend NVARCHAR(MAX) = 
'{
  "CharacterID" : 12,
  "CharacterName" : "Egwene Al''Vere",
  "IsFemale" : true,
  "Address" : {
    "AddressLine1" : "Emonds Field",
    "AddressLine2" : "The Two Rivers"
  },
  "Friends" : [
    {
      "CharacterName" : "Rand Al''Thor",
      "CharacterID" : 7,
      "IsFemale" : false
    },
    {
      "CharacterName" : "Perrin Aybara",
      "CharacterID" : 13,
      "IsFemale" : false
    }, 
    {
      "CharacterName" : "Matrim Cauthon",
      "CharacterID" : 16,
      "IsFemale" : false
    }
  ],
  "Children" : null
}';

SELECT
  JSON_VALUE(@json_friend, '$.CharacterID') AS CharacterID,
  JSON_VALUE(OJ.Value, '$.CharacterName') AS FriendCharacterName,
  JSON_VALUE(OJ.Value, '$.CharacterID') AS FriendCharacterID,
  JSON_VALUE(OJ.Value, '$.IsFemale') AS FriendIsFemale
FROM OPENJSON(@json_friend, '$.Friends') OJ;

JSON3 6

As you can see we can use JSON_VALUE to pull out the individual values from the JSON objects in the Friends key.

There is another way we can shred the JSON using OPENJSON and this is by specifying a schema for the data set returned. For example…

DECLARE @json_known NVARCHAR(MAX) =
'{
  "CharacterID" : 12,
  "CharacterName" : "Egwene Al''Vere",
  "IsFemale" : true,
  "Address" : {
    "AddressLine1" : "Emonds Field",
    "AddressLine2" : "The Two Rivers"
  },
  "Friends" : ["Rand Al''Thor", "Perrin Aybara", "Matrim Cauthon"],
  "Children" : null
}';

SELECT *
FROM OPENJSON(@json_known)
WITH 
(
  CharacterID INT '$.CharacterID',
  CharacterName VARCHAR(200) '$.CharacterName',
  IsFemale BIT '$.IsFemale',
  Address NVARCHAR(MAX) '$.Address' AS JSON,
  Friends NVARCHAR(MAX) '$.Friends' AS JSON,
  Children VARCHAR(500) '$.Children'
);

JSON3 7

We have to specify the data type for each of the values we want to return in the data set. If the value we are returning is a JSON object or an array then we need to include the AS JSON clause at the end of the column declaration. When we use AS JSON the data type must be NVARCHAR(MAX). If we try to use anything else we get the following error…

AS JSON option can be specified only for column of nvarchar(max) type in WITH clause.

We don’t need to include every column in the data set so the following is fine…

SELECT *
FROM OPENJSON(@json_known)
WITH 
(
  CharacterID INT '$.CharacterID',
  CharacterName VARCHAR(200) '$.CharacterName'
);

JSON3 8

We have to make sure the data types are correct. For example if we try to return a string value into an INT data type we get the following error as expected…

SELECT *
FROM OPENJSON(@json_known)
WITH 
(
  CharacterID INT '$.CharacterID',
  CharacterName INT '$.CharacterName'
);

Conversion failed when converting the nvarchar value 'Egwene Al'Vere' to data type int.

JSON is semi-structured so not every property in the table declaration needs to exist. For example, there is no CharacterAge property in the JSON but the following will still run correctly with NULL returned as the CharacterAge

SELECT *
FROM OPENJSON(@json_known)
WITH 
(
  CharacterID INT '$.CharacterID',
  CharacterAge INT '$.CharacterAge'
);

JSON3 9

If the JSON object contains embedded JSON or an array we are able to access values inside these by navigating down through the embedded objects or by using the ordinal position of the values in the arrays. We can pull all the values out of the @json_known object as follows…

SELECT *
FROM OPENJSON(@json_known)
WITH 
(
  CharacterID INT '$.CharacterID',
  CharacterName VARCHAR(200) '$.CharacterName',
  IsFemale BIT '$.IsFemale',
  AddressLine1 VARCHAR(500) '$.Address.AddressLine1',
  AddressLine2 VARCHAR(500) '$.Address.AddressLine2',
  AddressLine3 VARCHAR(500) '$.Address.AddressLine3',
  Friends0 VARCHAR(100) '$.Friends[0]',
  Friends1 VARCHAR(100) '$.Friends[1]',
  Friends2 VARCHAR(100) '$.Friends[2]',
  Friends3 VARCHAR(100) '$.Friends[3]',
  Children VARCHAR(500) '$.Children'
);

JSON3 10.PNG

Lax and Strict

The last thing I want to demonstrate are an additional couple of options we can use in the path parameter of the OPENJSON function. These options are lax and strict.

Strict means that the path specified in the second parameter of the OPENJSON function must exist and lax means that it doesn’t have to. Lax is the default if nothing is supplied so all the examples above use the lax option. Here are some examples using these options…

DECLARE @json VARCHAR(500) = '{"Hello": {"Name" : "Lax Strict Test"} }';

SELECT *
FROM OPENJSON(@json, '$.Goodbye');

SELECT *
FROM OPENJSON(@json, 'lax $.Goodbye');

SELECT *
FROM OPENJSON(@json, 'strict $.Hello');

SELECT *
FROM OPENJSON(@json, 'strict $.Goodbye');

JSON3 11

Property cannot be found on the specified JSON path.

As we can see the first two queries are exactly the same: this shows the lax option is the default. The third query (the first using the strict option) returns the data correctly because the Hello key exists. The fourth and final query returns an error because the Goodbye key does not exist and the strict option is used.

Advertisements

JSON – Part 2: Built in Functions

In a previous post I demonstrated how to use the FOR JSON clause and in this post I’m going to show some of the in built JSON functions. In a future post I’ll show how the OPENJSON function works.

JSON in SQL Server 2016

Built in support for JSON was introduced in SQL Server 2016. There is no native support meaning that there is no JSON data type in the same way that there is an XML data type but there are certain built in functions that allow us to manipulate JSON and convert data sets into JSON. As mentioned above, in a previous post I covered converting data sets into JSON using the FOR JSON clause, so let’s now look at a few demos extracting values out of and storing JSON data.

Validating JSON

When we use JSON in SQL Server we would usually have it in a text string.

Here’s a simple example…

DECLARE @json AS NVARCHAR(MAX) = N'{
   "Customer" : {
       "Id" : 100,
       "Name" : "Lionel Teddington",
       "Address" : {
           "AddrType" : "Home",
           "AddrLine1" : "1 The Road",
           "AddrLine2" : "My Village",
           "Postcode" : "AA1 2BB"
       }
    }
}';

So now we’ve got a JSON object stored in the variable @json.

One of the built in functions will return a boolean indicating if the JSON is correctly formatted or not.

SELECT ISJSON(@json) AS IsValidJson;

The 1 value returned by this function tells us that the JSON is valid.

If we try the following…

DECLARE @json AS NVARCHAR(MAX) = N'{
    "Customer" : {
        "Id" : 100,
        "Name" : "Lionel Teddington",
        "Address" : {
            "AddrType" : "Home",
            "AddrLine1" : "1 The Road",
            "AddrLine2" : "My Village,
            "Postcode" : "AA1 2BB"
        }
    }
}';
 
SELECT ISJSON(@json) AS IsValidJson;

… we can see the My Village value is missing the closing double quote which makes the JSON invalid, hence the value of 0 being returned.

Extracting values from JSON

There are two built in functions that we can use to extract values from a JSON object. The first is JSON_VALUE which can be used to extract a scalar value for a particular key in the JSON object. The second in JSON_QUERY which returns a JSON object (as a text string) that is embedded in values or arrays within the JSON.

If we use the valid JSON object from above we can extract the customer name as follows…

SELECT JSON_VALUE(@json, '$.Customer.Name') AS CustomerName;

As we can see the JSON_VALUE function accepts two parameters. The first is the JSON object itself and the second is the path to the key corresponding to the value we want to extract. The $ (dollar sign) is used to represent the JSON object specified in the first parameter and from this point we can navigate down through the key-value pairs to get the required value.

As mentioned above, the JSON is just a text string and the JSON_VALUE function doesn’t perform any validation. The same query will work if we use the invalid JSON object from the example above…

DECLARE @json AS NVARCHAR(MAX) = N'{
    "Customer" : {
        "Id" : 100,
        "Name" : "Lionel Teddington",
        "Address" : {
            "AddrType" : "Home",
            "AddrLine1" : "1 The Road",
            "AddrLine2" : "My Village,
            "Postcode" : "AA1 2BB"
        }
    }
}';

SELECT ISJSON(@json) AS IsValidJson;

-- if the JSON is invalid you can still query it
 SELECT JSON_VALUE(@json, '$.Customer.Name') AS CustomerName;

… but we can only extract values up to the point where the JSON object becomes invalid. In this example the JSON becomes invalid after the My Village value as there is a missing double quote. So if we try to extract the value for the Postcode which appears after the My Village value we see the following error…

SELECT ISJSON(@json) AS IsValidJson;

-- if the JSON is invalid you can still query it
 SELECT JSON_VALUE(@json, '$.Customer.Name') AS CustomerName;
 -- but only up to the point where the JSON becomes invalid
 SELECT JSON_VALUE(@json, '$.Customer.Address.Postcode') AS Postcode;

(1 row affected)

(1 row affected)
 Msg 13609, Level 16, State 2, Line 41
 JSON text is not properly formatted. Unexpected character '"' is found at position 160.

The JSON is treated as case sensitive so the following returns NULL on the valid JSON example because the Name key has a lower case n

SELECT JSON_VALUE(@json, '$.Customer.name') AS CustomerName;

If we look at the valid JSON example we can see that the Address key has a JSON object as it’s value. We can’t return this with the JSON_VALUE function and this would just return NULL, but we can use JSON_QUERY as follows…

SELECT JSON_QUERY(@json, '$.Customer.Address') AS AddressJson;

Like JSON_VALUE this function accepts a JSON object as the first parameter and then a path to the embedded JSON as the second parameter.

Modifying JSON objects

The fourth and final function I’m going to demonstrate is the JSON_MODIFY function which allows us to modify values for certain keys inside the supplied JSON.

For example, we can run the following to change the CustomerID value from 100 to 101…

SELECT JSON_MODIFY(@json, '$.Customer.Id', '101');

JSON2 8

As you can see the JSON_MODIFY function returns the modified JSON object. It’s worth noting that this function doesn’t modify the value in the existing JSON object, it just returns the modified JSON.

We can run the following to show this is the case…

SELECT JSON_MODIFY(@json, '$.Customer.Id', '101');
-- we don't actually see the data changed in the variable. modify just returns the amended JSON
SELECT JSON_VALUE(@json, '$.Customer.Id') AS NewCustomerId;

JSON2 9

… and we can see the CustomerID in @json is still 100.

What we can do is declare another variable and store the results of the JSON_MODIFY function there and use that variable in any further processing…

DECLARE @modifiedJson NVARCHAR(MAX);
SELECT @modifiedJson = JSON_MODIFY(@json, '$.Customer.Id', '101');
SELECT JSON_VALUE(@modifiedJson, '$.Customer.Id') AS NewCustomerId;

JSON2 10

Combining JSON_VALUE and JSON_QUERY

Now let’s have a look at a more complicated piece of JSON. In this example the Customer key has an array of JSON objects as it’s value. If we try to pull out the Customer Name value using the JSON_VALUE function we just get a NULL returned…

DECLARE @json AS NVARCHAR(MAX) = N'{
  "Customer" : [
    {
      "Id" : 100,
      "Name" : "Lionel Teddington",
      "Address" : {
        "AddrType" : "Home", 
        "AddrLine1" : "1 The Road",
        "AddrLine2" : "My Village",
        "Postcode" : "AA1 2BB"
      }
    },
    {
      "Id" : 101,
      "Name" : "Carolina Sweetcorn",
      "Address" : {
        "AddrType" : "Home", 
        "AddrLine1" : "1 The Road",
        "AddrLine2" : "My Village",
        "Postcode" : "AA1 2BB"
      }
    }
  ],
  "Shop" : {
    "ShopName" : "The best shop in the world",
    "ShopCode" : "0385920",
    "ShopAddress" : {
      "ShopPostCode" : "AA1 2BC"
    }
  }
}';

SELECT ISJSON(@json)

SELECT JSON_VALUE(@json, '$.Customer.Name') AS CustomerName;

JSON2 11

What we need to do is get the array of JSON objects using JSON_QUERY and then use JSON_VALUE to extract the required values from there…

-- must use JSON_QUERY for arrays
SELECT JSON_QUERY(@json, '$.Customer') AS CustomerJSON;

SELECT JSON_VALUE(
  JSON_QUERY(@json, '$.Customer'),
  '$.Name'
) AS CustomerName;

JSON2 12

The first SELECT statement shows the array being returned by the JSON_QUERY function but the second SELECT statement is still returning NULL.

This is because with arrays we need to specify which ordinal in the array we want to return the value for.

We can return the name for both customers in our array of JSON objects as follows…

SELECT
  JSON_VALUE(
    JSON_QUERY(@json, '$.Customer'),
    '$[0].Name'
  ) AS FirstCustomerName,
  JSON_VALUE(
    JSON_QUERY(@json, '$.Customer'),
    '$[1].Name'
  ) AS SecondCustomerName;

JSON2 13

Please note that the first ordinal is 0 and not 1

We have two JSON objects in our example but we still need to include the ordinal even if there is only one.

Storing Valid JSON

As previously mentioned there is no JSON data type but we may want to have some validation on any JSON objects that we store in our database.

Let’s create a new table that has just one column that we want to store some JSON in…

CREATE TABLE dbo.JSONDemo
(
  JsonDoc NVARCHAR(MAX)
);

… and now let’s insert some invalid JSON – note the missing double quote after the World value…

INSERT INTO dbo.JSONDemo
(
  JsonDoc
)
VALUES
(N'{ "Hello" : "World}');

SELECT *
FROM dbo.JSONDemo JD

JSON2 14

This insert works without any errors and note the missing double quote in the results.

Now let’s clear out the table and add a check constraint using the ISJSON function…

TRUNCATE TABLE dbo.JSONDemo;

ALTER TABLE dbo.JSONDemo ADD CONSTRAINT CK_ValidJsonOnly CHECK (ISJSON(JsonDoc) = 1);

This means that now only valid JSON can be added to our table. If we try to insert the invalid JSON we get the following…

INSERT INTO dbo.JSONDemo
(
  JsonDoc
)
VALUES
(N'{ "Hello" : "World}');

Msg 547, Level 16, State 0, Line 146
The INSERT statement conflicted with the CHECK constraint "CK_ValidJsonOnly". The conflict occurred in database "SimonBlog", table "dbo.JSONDemo", column 'JsonDoc'.
The statement has been terminated.

However, if we correct the JSON and add the missing closing double quote we can insert successfully…

INSERT INTO dbo.JSONDemo
(
  JsonDoc
)
VALUES
(N'{ "Hello" : "World"}');

SELECT *
FROM dbo.JSONDemo JD

JSON2 15

The insert was successful and note all double quotes are now there.

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.