Exploring the Power of the JSON_OBJECT() Function

SQL Server 2022 enhances its JSON capabilities with the JSON_OBJECT() function, providing a streamlined approach to transforming SQL query results into JSON format directly within SQL queries. This functionality is invaluable for developers working in environments where SQL Server interacts with web services, applications, or systems that consume or produce JSON data. To illustrate the practical application of the JSON_OBJECT() function, we’ll use the dbo.Employee table as a reference throughout this article. Let’s begin by preparing our dataset.

Setting Up the dbo.Employee Table

First, we’ll create the dbo.Employee table and populate it with sample data. This setup will serve as the foundation for our examples.

-- Creation of dbo.Employee table
CREATE TABLE dbo.Employee (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Position NVARCHAR(50),
    Department NVARCHAR(50),
    Salary INT
);

-- Inserting sample data into dbo.Employee
INSERT INTO dbo.Employee (EmployeeID, FirstName, LastName, Position, Department, Salary)
VALUES
    (1, 'John', 'Doe', 'Software Engineer', 'Engineering', 70000),
    (2, 'Jane', 'Smith', 'Project Manager', 'Project Management', 85000),
    (3, 'William', 'Johnson', 'Data Analyst', 'Data Science', 75000),
    (4, 'Emma', 'Brown', 'UI/UX Designer', 'Design', 68000),
    (5, 'Michael', 'Davis', 'DevOps Engineer', 'Engineering', 90000);

With our table ready, let’s explore how to use the JSON_OBJECT() function with the dbo.Employee table through a series of examples.

Example 1: Creating a Simple JSON Object

In this first example, we create a JSON object for each employee that includes their ID, full name, and position.

SQL Query:

SELECT 
    JSON_OBJECT(
        'EmployeeID':CAST(EmployeeID AS NVARCHAR(10)),
        'FullName':CONCAT(FirstName, ' ', LastName),
        'Position':Position
    ) AS EmployeeJSON
FROM dbo.Employee;

JSON Output:

{"EmployeeID":"1","FullName":"John Doe","Position":"Software Engineer"}
{"EmployeeID":"2","FullName":"Jane Smith","Position":"Project Manager"}
{"EmployeeID":"3","FullName":"William Johnson","Position":"Data Analyst"}
{"EmployeeID":"4","FullName":"Emma Brown","Position":"UI\/UX Designer"}
{"EmployeeID":"5","FullName":"Michael Davis","Position":"DevOps Engineer"}

Example 2: Nesting JSON Objects

We explore the creation of nested JSON objects. Here, we structure the JSON to include a nested object for the department, showcasing a more complex data hierarchy.

SQL Query:

SELECT 
    JSON_OBJECT(
        'EmployeeID': CAST(EmployeeID AS NVARCHAR(10)),
        'FullName': CONCAT(FirstName, ' ', LastName),
        'Position': Position,
        'Department': JSON_OBJECT('Name': Department, 'BudgetCode': 'XYZ123')
    ) AS EmployeeJSON
FROM dbo.Employee;

JSON Output:

For an employee in the Engineering department, the JSON output would appear as follows:

{
    "EmployeeID": "1",
    "FullName": "John Doe",
    "Position": "Software Engineer",
    "Department": {
        "Name": "Engineering",
        "BudgetCode": "XYZ123"
    }
}

Through these examples, it’s clear how the JSON_OBJECT() function can be utilized to produce structured JSON data directly from SQL queries, simplifying the integration between SQL Server and applications that operate with JSON. Whether you’re creating simple or complex JSON objects, JSON_OBJECT() offers the flexibility and power to meet your data serialization needs.