This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Write advanced T-SQL code
      --> Write queries that include JSON functions

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

JSON (JavaScript Object Notation) has become one of the most common formats for exchanging and storing structured data in modern applications. SQL Server and Azure SQL Database provide native JSON support that allows developers to parse, query, modify, and generate JSON data without requiring a separate document database.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand how to use T-SQL JSON functions to work with JSON documents stored in SQL Server tables or received from external applications and services. JSON capabilities are particularly valuable when integrating relational databases with REST APIs, cloud services, AI applications, and modern web applications.

Unlike XML, SQL Server does not have a dedicated JSON data type (in generally available releases covered by the current DP-800 learning content). Instead, JSON documents are typically stored in nvarchar columns and processed using built-in JSON functions.

This article covers the JSON functionality emphasized in the current Microsoft Learn curriculum, including:

• Understanding JSON in SQL Server
• Validating JSON documents
• Extracting scalar values
• Extracting objects and arrays
• Parsing JSON into relational rows
• Modifying JSON documents
• Returning JSON from queries
• Performance considerations
• AI-enabled database scenarios
• Best practices

Understanding JSON in SQL Server

JSON represents data as key-value pairs and arrays.

Example JSON document:

{
  "CustomerID": 1001,
  "Name": "John Smith",
  "Email": "john@contoso.com",
  "Orders": [
    {
      "OrderID": 501,
      "Amount": 250.00
    },
    {
      "OrderID": 502,
      "Amount": 120.00
    }
  ]
}

SQL Server stores JSON as plain text but provides functions that understand the JSON structure.

JSON Support in SQL Server

The primary JSON features include:

• ISJSON()
• JSON_VALUE()
• JSON_QUERY()
• JSON_MODIFY()
• OPENJSON
• FOR JSON

These functions allow developers to:

• Validate JSON
• Retrieve values
• Retrieve arrays and objects
• Update JSON documents
• Convert JSON into relational tables
• Generate JSON output

ISJSON()

ISJSON() determines whether a string contains valid JSON.

Syntax:

ISJSON(expression)

Example:

SELECT ISJSON('{"Name":"John"}');

Result:

1

Invalid JSON returns:

0

Common use cases include:

• Data validation
• Import validation
• Preventing malformed JSON from entering the database

JSON_VALUE()

JSON_VALUE() extracts a single scalar value from a JSON document.

Syntax:

JSON_VALUE(expression, path)

Example:

SELECT JSON_VALUE(
'{
   "Customer":
   {
      "Name":"John Smith"
   }
}',
'$.Customer.Name');

Result:

John Smith

JSON_VALUE() returns values such as:

• Strings
• Numbers
• Dates
• Booleans

It does not return JSON objects or arrays.

JSON Path Expressions

JSON functions use path expressions to locate data.

Examples:

Understanding JSON path syntax is an important DP-800 exam objective.

JSON_QUERY()

JSON_QUERY() extracts an object or an array instead of a scalar value.

Example:

SELECT JSON_QUERY(
'{
   "Orders":
   [
      {"OrderID":1},
      {"OrderID":2}
   ]
}',
'$.Orders');

Result:

[
   {"OrderID":1},
   {"OrderID":2}
]

Use JSON_QUERY() whenever the requested value is another JSON object or array.

JSON_VALUE() vs. JSON_QUERY()

Choosing the correct function is a common exam topic.

OPENJSON

OPENJSON converts JSON into relational rows and columns.

Example:

DECLARE @Orders nvarchar(max) =
'[
    {"OrderID":101,"Amount":150},
    {"OrderID":102,"Amount":250}
]';
SELECT *
FROM OPENJSON(@Orders);

Result:

OPENJSON WITH Clause

The WITH clause maps JSON properties to columns.

Example:

DECLARE @Orders nvarchar(max) =
'[
    {"OrderID":101,"Amount":150},
    {"OrderID":102,"Amount":250}
]';
SELECT *
FROM OPENJSON(@Orders)
WITH
(
    OrderID int,
    Amount decimal(10,2)
);

Result:

This is the preferred method when importing structured JSON into SQL tables.

JSON_MODIFY()

JSON_MODIFY() updates a JSON document.

Example:

DECLARE @Customer nvarchar(max)=
'{"Name":"John","City":"Seattle"}';
SELECT JSON_MODIFY(
@Customer,
'$.City',
'Orlando');

Result:

{
   "Name":"John",
   "City":"Orlando"
}

JSON_MODIFY() can:

• Update values
• Insert properties
• Delete properties by assigning NULL

FOR JSON

FOR JSON converts SQL query results into JSON.

Example:

SELECT
    CustomerID,
    Name
FROM Customers
FOR JSON AUTO;

Output:

[
  {
      "CustomerID":1,
      "Name":"John"
  },
  {
      "CustomerID":2,
      "Name":"Mary"
  }
]

FOR JSON AUTO vs. FOR JSON PATH

FOR JSON AUTO

Automatically generates JSON based on table structure.

Example:

SELECT CustomerID, Name
FROM Customers
FOR JSON AUTO;

Little customization is available.

FOR JSON PATH

Provides complete control over the generated JSON structure.

Example:

SELECT
CustomerID AS "Customer.ID",
Name AS "Customer.Name"
FROM Customers
FOR JSON PATH;

This allows nested objects and custom property names.

Working with Nested JSON

Example:

{
   "Customer":
   {
      "Name":"John",
      "Address":
      {
         "City":"Orlando"
      }
   }
}

Retrieve the city:

SELECT JSON_VALUE(
@Customer,
'$.Customer.Address.City');

Loading JSON into Tables

Example:

INSERT INTO Orders(OrderID, Amount)
SELECT OrderID, Amount
FROM OPENJSON(@Orders)
WITH
(
    OrderID int,
    Amount decimal(10,2)
);

This approach is frequently used when consuming REST APIs.

Returning JSON from Stored Procedures

Stored procedures often return JSON to client applications.

Example:

SELECT *
FROM Customers
FOR JSON PATH;

Applications can consume the JSON without additional transformation.

JSON and Azure Services

JSON is widely used with:

• Azure Functions
• Azure Logic Apps
• Azure App Service
• Azure API Management
• REST APIs
• Power Apps
• Power Automate

JSON enables efficient communication between SQL databases and cloud-based applications.

AI-Enabled Database Scenarios

JSON plays a significant role in AI-enabled solutions because many AI services exchange information using JSON documents.

Common scenarios include:

• Receiving prompts from client applications
• Storing AI model responses
• Logging chatbot conversations
• Storing document metadata
• Integrating Azure AI services
• Consuming REST APIs
• Passing structured data to Retrieval-Augmented Generation (RAG) pipelines
• Returning AI-generated content to applications

For example, a SQL stored procedure might accept a JSON request from an application, extract values with JSON_VALUE() or OPENJSON, query relational data, and return results as JSON using FOR JSON PATH.

Emerging JSON Functions (SQL Server 2025 and Azure SQL Database)

Recent versions of Azure SQL Database and SQL Server introduce additional JSON functions that make it easier to construct, aggregate, and search JSON data directly within SQL queries. While these functions are newer than the core JSON functions covered earlier, they represent the future direction of SQL Server’s native JSON capabilities and are useful to understand.

These functions include:

• JSON_OBJECT()
• JSON_ARRAY()
• JSON_ARRAYAGG()
• JSON_OBJECTAGG()
• JSON_CONTAINS()

JSON_OBJECT()

JSON_OBJECT() creates a JSON object directly from key-value pairs.

Instead of manually concatenating strings, SQL Server automatically generates properly formatted JSON.

Syntax:

JSON_OBJECT(
    'key1': value1,
    'key2': value2
)

Example:

SELECT JSON_OBJECT(
    'CustomerID': CustomerID,
    'Name': CustomerName,
    'City': City
)
FROM Customers;

Possible output:

{
    "CustomerID": 101,
    "Name": "John Smith",
    "City": "Seattle"
}

Benefits

• Simpler than string concatenation
• Automatically escapes special characters
• Produces valid JSON
• Easier to read and maintain

JSON_ARRAY()

JSON_ARRAY() creates a JSON array from one or more values.

Syntax:

JSON_ARRAY(value1, value2, value3)

Example:

SELECT JSON_ARRAY(
    'SQL',
    'Azure',
    'AI',
    'JSON'
);

Output:

[
    "SQL",
    "Azure",
    "AI",
    "JSON"
]

Arrays may contain:

• Strings
• Numbers
• Boolean values
• NULL values
• Nested JSON objects

This function is particularly useful when returning lists to applications and APIs.

JSON_ARRAYAGG()

JSON_ARRAYAGG() aggregates multiple rows into a single JSON array.

It performs a role similar to STRING_AGG(), but returns properly formatted JSON instead of plain text.

Example:

SELECT JSON_ARRAYAGG(CustomerName)
FROM Customers;

Output:

[
    "John",
    "Mary",
    "Susan",
    "David"
]

It can also aggregate JSON objects.

Example:

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'ID': CustomerID,
        'Name': CustomerName
    )
)
FROM Customers;

Output:

[
    {
        "ID":101,
        "Name":"John"
    },
    {
        "ID":102,
        "Name":"Mary"
    }
]

Common Uses

• REST API responses
• AI service payloads
• Returning collections of objects
• Building hierarchical JSON documents

JSON_OBJECTAGG()

JSON_OBJECTAGG() aggregates multiple rows into a single JSON object.

Each row contributes a key-value pair.

Example:

SELECT JSON_OBJECTAGG(
    DepartmentName : EmployeeCount
)
FROM DepartmentSummary;

Possible output:

{
    "Sales":42,
    "Finance":18,
    "HR":11
}

This function is useful when applications require lookup-style JSON objects rather than arrays.

Common scenarios include:

• Configuration settings
• Summary statistics
• Name/value collections
• Metadata dictionaries

JSON_CONTAINS()

JSON_CONTAINS() determines whether a JSON document contains a specified value or object.

Example:

SELECT JSON_CONTAINS(
    '{"Skills":["SQL","Azure","AI"]}',
    '"Azure"',
    '$.Skills'
);

Result:

1

A return value of:

• 1 indicates the value exists.
• 0 indicates it does not exist.

Unlike JSON_VALUE(), which retrieves a value, JSON_CONTAINS() is intended for searching JSON documents.

Typical uses include:

• Searching arrays
• Validating configuration values
• Checking permissions stored as JSON
• Verifying tags or categories
• Filtering semi-structured data

Comparing the JSON Functions

AI-Enabled Database Scenarios

These newer JSON functions are especially useful in AI-enabled database solutions because AI applications frequently exchange complex JSON payloads.

Examples include:

• Creating structured prompts for large language models (LLMs)
• Returning Retrieval-Augmented Generation (RAG) results as JSON arrays
• Building JSON responses for Azure AI Foundry or Azure OpenAI applications
• Aggregating search results into JSON collections for APIs
• Constructing metadata objects for vector search and embeddings
• Verifying whether AI-generated JSON responses contain required fields or values

By generating JSON natively within SQL Server, these functions reduce the need for application-side serialization and simplify integrations with cloud services and AI workflows.

Performance Considerations

Because JSON is stored as text, SQL Server must parse the document during queries.

Performance can be improved by:

• Storing only necessary JSON data
• Using computed columns that extract frequently queried properties
• Creating indexes on persisted computed columns
• Avoiding repeated parsing of large JSON documents
• Using OPENJSON with a WITH clause for structured imports

Best Practices

• Validate incoming JSON using ISJSON().
• Use JSON_VALUE() for scalar values.
• Use JSON_QUERY() for arrays and objects.
• Use OPENJSON to convert JSON into relational rows.
• Use JSON_MODIFY() to update JSON documents.
• Use FOR JSON PATH when customized output is required.
• Store JSON only when relational columns are not appropriate.
• Index frequently queried JSON properties through computed columns.
• Validate JSON path expressions during development.
• Keep JSON documents reasonably sized to improve performance.

Common Exam Tips

For the DP-800 exam, remember the following:

• SQL Server stores JSON in nvarchar columns.
• ISJSON() validates JSON.
• JSON_VALUE() returns scalar values.
• JSON_QUERY() returns objects and arrays.
• JSON_MODIFY() updates JSON documents.
• OPENJSON converts JSON into relational rows.
• OPENJSON WITH maps JSON properties to typed columns.
• FOR JSON AUTO automatically formats query results.
• FOR JSON PATH provides greater control over the JSON output.
• JSON is commonly used when integrating SQL Server with cloud services, APIs, and AI applications.

Practice Exam Questions

Question 1

Which function validates whether a string contains properly formatted JSON?

A. JSON_QUERY()

B. JSON_MODIFY()

C. OPENJSON

D. ISJSON()

Answer: D

Explanation: ISJSON() returns 1 for valid JSON and 0 for invalid JSON, making it useful for validating incoming data.

Question 2

Which function should you use to extract a single scalar value such as a customer’s name from a JSON document?

A. JSON_QUERY()

B. JSON_VALUE()

C. OPENJSON()

D. FOR JSON

Answer: B

Explanation: JSON_VALUE() returns a single scalar value such as a string, number, or Boolean from a specified JSON path.

Question 3

A developer needs to return an entire JSON array from a document. Which function is appropriate?

A. JSON_QUERY()

B. JSON_VALUE()

C. ISJSON()

D. JSON_MODIFY()

Answer: A

Explanation: JSON_QUERY() returns JSON objects and arrays, whereas JSON_VALUE() returns only scalar values.

Question 4

Which T-SQL feature converts JSON data into relational rows and columns?

A. JSON_VALUE()

B. JSON_QUERY()

C. OPENJSON

D. FOR JSON PATH

Answer: C

Explanation: OPENJSON parses JSON text and returns rows that can be further mapped into relational columns using the WITH clause.

Question 5

Which statement about FOR JSON PATH is correct?

A. It validates JSON documents.

B. It converts JSON into relational tables.

C. It provides control over the structure of generated JSON output.

D. It can only return scalar values.

Answer: C

Explanation: FOR JSON PATH allows developers to customize property names and create nested JSON structures.

Question 6

What is the primary purpose of JSON_MODIFY()?

A. Validate JSON syntax.

B. Retrieve a scalar value.

C. Return an array.

D. Update or insert values within a JSON document.

Answer: D

Explanation: JSON_MODIFY() changes JSON content by updating, inserting, or deleting properties.

Question 7

When importing data from a REST API into SQL Server, which approach provides the most structured mapping between JSON properties and SQL columns?

A. JSON_QUERY()

B. OPENJSON with a WITH clause

C. ISJSON()

D. FOR JSON AUTO

Answer: B

Explanation: The WITH clause allows OPENJSON to map JSON properties directly into strongly typed SQL columns.

Question 8

Which JSON path expression returns the value of the Name property within the Customer object?

A. $.Name.Customer

B. Customer.Name

C. $.Customer.Name

D. $[Customer][Name]

Answer: C

Explanation: JSON path expressions begin at the root ($) and navigate through object properties using dot notation.

Question 9

Why are computed columns often used with JSON data?

A. They convert JSON into XML.

B. They eliminate the need for JSON functions.

C. They allow frequently accessed JSON values to be indexed for improved query performance.

D. They automatically validate JSON syntax.

Answer: C

Explanation: Persisted computed columns can extract JSON properties using JSON_VALUE(), enabling indexes to improve query performance.

Question 10

How are SQL Server JSON functions commonly used in AI-enabled database solutions?

A. They replace relational tables entirely.

B. They create machine learning models directly.

C. They eliminate the need for APIs.

D. They parse, transform, and generate structured JSON exchanged between SQL databases, AI services, REST APIs, and applications.

Answer: D

Explanation: AI services commonly exchange structured JSON payloads. SQL Server JSON functions enable applications to consume, transform, store, and return this data efficiently.

Go to the DP-800 Exam Prep Hub main page