json_object and json_array Functions SQL Server 2022 Tutorial

By: Daniel Calbimonte   |   Updated: 2023-02-03   |   Comments (2)   |   Related: > SQL Server 2022

Problem

Each day, JSON is becoming more popular and used in many systems. Is there a way to build your own JSON objects using T-SQL as opposed to Python?

Solution

In SQL Server 2022, a new function named JSON_OBJECT was introduced to construct JSON objects using T-SQL. Also, the JSON_ARRAY was included to create arrays in JSON. This tutorial will show how to work with these new functions.

The tutorial will include the following topics:

• JSON Syntax
• JSON_OBJECT attribute example with strings
• JSON_OBJECT attribute example with numbers
• JSON_OBJECT attribute example with a null value
• JSON_OBJECT absent on null option
• JSON_OBJECT with multiple attributes
• JSON_ARRAY example with numbers
• JSON_ARRAY example with strings
• JSON_OBJECT with an array of objects
• Objects with object attributes
• How to get information from tables and functions with JSON_OBJECT

Requirements

1. SQL Server 2022 installed
2. SSMS installed
3. For the last example, I will use the Adventureworks database.

JSON Introduction

JSON stands for JavaScript Object Notation. It is a format used for purposes similar to an XML file. It is used to interchange data using REST APIs, web services, and other applications.

JSON Syntax

A simple attribute in JSON is like this:

{"name":"Robert"}

Note that brace brackets are used. The name of the attribute is used with double quotes followed by a colon, and then the value of the attribute, which uses double quotes if it is a string.

If it is a number, it does not require double quotes as shown below.

{"age":57}

To handle null values, you use null.

{"Name":null}

The following example shows an object named actors that stores an actor's attributes (name, last name, age):

{"name":"Robert","lastname:Downey", "age":57}

Note: The attributes are separated by commas.

In JSON, you can also use arrays. Arrays use square brackets. The following example shows how to work with arrays. For numbers, you do not need double quotes around the values.

[1,2,4]

For arrays with strings, double quotes are required around the values.

["John","Bran","Sansa"]

For multiple records, we can use something like this:

[
{"name":"Robert","lastname":"Downey","age":57},
{"name":"Chris","lastname":"Hemsworth","age":39}
]

We used square brackets and each item (in this example, actor) is separated by commas.

For hierarchical data, you can have something like this. In this example, I will create a small hierarchical structure. The mother (Rhaenrya Targaryen from the House of the Dragon) has two children: Jacaerys and Lucerys.

The JSON syntax would be something like this:

{
"Name":"Rhaenyra",
"Lastname":"Targaryen",
"Children":[
   {
     "name":"Jacaerys",
     "lastname":"Velaryon",
     "age":20
   },
   {
     "name":"Lucerys",
     "lastname":"Velaryon",
     "age":18
   }
 ]
}

Now, we will learn how to build these JSON structures using JSON_OBJECT and JSON_ARRAY in T-SQL.

JSON_OBJECT Attribute Example with Strings

The following example creates the attribute name with the value Robert in JSON format.

SELECT JSON_OBJECT('name':'Robert') attribute

Note: The brace brackets are not used, and double quotes are replaced by single quotes.

JSON_OBJECT Attribute Example with Numbers

This example will use number attributes.

SELECT JSON_OBJECT('age':57) attribute

Note that the numbers do not require quotes.

JSON_OBJECT Attribute Example with a Null Value

We can use null values.

SELECT JSON_OBJECT('name':null) attribute

The null values do not require quotes.

JSON_OBJECT Absent on Null Option

The absent on null option will not show the attribute if the value is null.

In this example, if the value is not null, then the value will be displayed:

SELECT JSON_OBJECT('name':2 ABSENT ON NULL) attribute

On the other hand, if a NULL value is included in the JSON, the value will not be displayed:

SELECT JSON_OBJECT('name':null ABSENT ON NULL) attribute

JSON_OBJECT with Multiple Attributes

When multiple attributes are used, they are separated by commas.

SELECT JSON_OBJECT('name':'Robert','lastname':'Downey','age':57) attributes

When multiple attributes are used, they are separated by commas.

JSON_ARRAY Example with Numbers

In this example, we will show how to create a JSON ARRAY using the JSON_ARRAY function.

SELECT JSON_ARRAY(1,2,4) myArray

JSON_ARRAY Example with Strings

The JSON_ARRAY function uses single quotes for strings.

SELECT JSON_ARRAY('John','Bran','Sansa') myArray

JSON_OBJECT with an Array of Objects

The following example shows how to display multiple actors. For this example, we will create an array with two items. Each item has its attributes. In this example, the items are the actors Robert and Chris.

SELECT JSON_ARRAY
  (JSON_OBJECT('name':'Robert','lastname':'Downey','age':57), 
  (JSON_OBJECT('name':'Chris','lastname':'Hemsworth','age':39))) array

If you want to format the data to make it easier to read, you can use a JSON parser. There are many free tools online. With these tools, your data will be easier to read.

Format without the Parser

[{"name":"Robert","lastname":"Downey","age":57},{"name":"Chris","lastname":"Hemsworth","age":39}]

Format with the Parser

[
  {
    "name":"Robert",
    "lastname":"Downey",
    "age":57
  },
  {
    "name":"Chris",
    "lastname":"Hemsworth",
    "age":39
  }
]

Objects with Object Attributes

In some scenarios, you have hierarchical attributes. This example shows the mother and children hierarchical example explained before with Rhaenrya and her children.

SELECT JSON_OBJECT(
   'Name':'Rhaenyra',
   'Lastname':'Targaryen',
   'Children':
      JSON_ARRAY
         (JSON_OBJECT('name':'Jacaerys','lastname':'Velaryon', 'age':20),
         (JSON_OBJECT('name':'Lucerys','lastname':'Velaryon', 'age':18)))
) json

The output of this example is the following:

{
"Name":"Rhaenyra",
"Lastname":"Targaryen",
"Children":[
   {
     "name":"Jacaerys",
     "lastname":"Velaryon",
     "age":20
   },
   {
     "name":"Lucerys",
     "lastname":"Velaryon",
     "age":18
   }
 ]
}

Getting Information from Tables and Functions with JSON_OBJECT

Finally, we have an example using the Person.person table data from the Adventureworks database and the GETDATE function.

SELECT JSON_OBJECT('Firstname':FirstName,'Lastname':LastName,'Time':getdate()) attribute
FROM [Person].[Person]

Next Steps

• In this tutorial, we use the JSON_Object to create objects in JSON and the JSON_Array function to create JSON arrays. The syntax is simple, but if you have a complex and nested structure, you should indent your code to make it easier to use and read.
• Try these new functions on your systems.

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips