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%)
--> Design and implement database objects
--> Design and implement JSON columns and indexes
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 widely used formats for exchanging data between applications, web services, APIs, cloud platforms, and AI solutions. Modern database applications frequently need to store, query, validate, and index JSON documents alongside traditional relational data.
SQL Server and Azure SQL Database provide extensive built-in support for working with JSON, allowing developers to:
- Store JSON documents
- Validate JSON data
- Query individual properties
- Modify JSON documents
- Convert relational data to JSON
- Convert JSON into relational tables
- Improve performance by indexing JSON values
For the DP-800: Developing AI-Enabled Database Solutions certification exam, understanding how JSON is stored, queried, and indexed is an important skill because many AI-enabled applications exchange information using JSON.
Why Use JSON?
JSON offers a flexible way to store semi-structured data that doesn’t fit neatly into fixed relational tables.
Common scenarios include:
- REST API payloads
- Application configuration
- Product catalogs
- Customer preferences
- Event logs
- IoT telemetry
- AI prompts and responses
- Chat conversation history
- Metadata storage
Rather than creating dozens of optional columns, JSON allows variable attributes to be stored in a single column.
JSON Support in SQL Server
Unlike some database systems, SQL Server stores JSON as text rather than as a dedicated JSON data type.
Typically, JSON documents are stored in:
- NVARCHAR(MAX)
- NVARCHAR(n)
Example:
CREATE TABLE Orders( OrderID INT PRIMARY KEY, CustomerInfo NVARCHAR(MAX));
A row might contain:
{ "CustomerID": 125, "Name": "John Smith", "City": "Seattle", "LoyaltyLevel": "Gold"}
Although stored as text, SQL Server provides built-in functions that understand JSON syntax.
Validating JSON
Before using JSON data, developers should ensure it contains valid JSON.
SQL Server provides the ISJSON() function.
Example:
SELECT ISJSON(CustomerInfo)FROM Orders;
Returns:
- 1 = Valid JSON
- 0 = Invalid JSON
Example using a CHECK constraint:
ALTER TABLE OrdersADD CONSTRAINT CK_ValidJSONCHECK (ISJSON(CustomerInfo) = 1);
This prevents invalid JSON from being stored.
Extracting Values with JSON_VALUE()
The JSON_VALUE() function extracts a scalar value from a JSON document.
Example:
SELECTJSON_VALUE(CustomerInfo,'$.Name')FROM Orders;
Result:
John Smith
Common uses include:
- Filtering
- Sorting
- Displaying attributes
- Reporting
Extracting Objects and Arrays with JSON_QUERY()
JSON_QUERY() retrieves an object or array rather than a scalar value.
Example JSON:
{ "CustomerID": 25, "Orders": [ {"OrderID":1}, {"OrderID":2} ]}
Query:
SELECTJSON_QUERY(CustomerInfo,'$.Orders')FROM Orders;
Returns the JSON array.
Updating JSON with JSON_MODIFY()
JSON_MODIFY() updates properties without replacing the entire document.
Example:
UPDATE OrdersSET CustomerInfo =JSON_MODIFY(CustomerInfo,'$.City','Chicago');
Useful when applications maintain customer preferences or AI-generated metadata.
Parsing JSON into Rows with OPENJSON()
OPENJSON converts JSON into relational rows.
Example:
SELECT *FROM OPENJSON(@json);
Example JSON:
{ "Name":"Alice", "City":"Boston"}
Returns:
| Key | Value |
|---|---|
| Name | Alice |
| City | Boston |
Using WITH to Define a Schema
OPENJSON becomes much more useful when mapping JSON to columns.
Example:
SELECT *FROM OPENJSON(@json)WITH( Name NVARCHAR(100), City NVARCHAR(100), Age INT);
This converts JSON directly into relational columns.
Producing JSON with FOR JSON
SQL Server can return query results as JSON.
Example:
SELECT CustomerID, Name, CityFROM CustomersFOR JSON AUTO;
Output:
[ { "CustomerID":1, "Name":"Alice", "City":"Boston" }]
FOR JSON PATH
FOR JSON PATH provides greater control over the structure.
Example:
SELECT CustomerID, NameFROM CustomersFOR JSON PATH;
Developers commonly use this when building REST APIs.
Common JSON Functions
| Function | Purpose |
|---|---|
| ISJSON() | Validate JSON |
| JSON_VALUE() | Return scalar value |
| JSON_QUERY() | Return object or array |
| JSON_MODIFY() | Update JSON |
| OPENJSON() | Convert JSON into rows |
| FOR JSON | Produce JSON output |
These functions appear frequently in Microsoft documentation and certification objectives.
Why JSON Indexing Matters
JSON itself is stored as text.
Without indexing, SQL Server must scan every JSON document to locate requested values.
This becomes expensive for large tables.
Example:
SELECT *FROM OrdersWHERE JSON_VALUE(CustomerInfo,'$.City')='Seattle';
Without indexing, SQL Server evaluates JSON_VALUE() for every row.
Indexing JSON Data
Because JSON is stored in NVARCHAR columns, SQL Server cannot directly create an index on an arbitrary JSON property.
Instead, developers create computed columns that expose JSON properties and then index those computed columns.
This is the recommended approach for SQL Server and Azure SQL Database and is an important DP-800 exam objective.
Creating a Computed Column
Example:
ALTER TABLE OrdersADD CustomerCity ASJSON_VALUE(CustomerInfo,'$.City');
SQL Server now treats CustomerCity as a regular column.
Creating an Index
Once the computed column exists, it can be indexed.
Example:
CREATE INDEX IX_Orders_CustomerCityON Orders(CustomerCity);
Queries filtering on CustomerCity can now use the index rather than scanning every JSON document.
Persisted Computed Columns
Computed columns may be marked PERSISTED.
Example:
ALTER TABLE OrdersADD CustomerCityAS JSON_VALUE(CustomerInfo,'$.City')PERSISTED;
Benefits include:
- Value stored physically
- Faster reads
- Reduced recalculation
Trade-offs include:
- Increased storage
- Slightly slower INSERT/UPDATE operations
JSON Path Expressions
SQL Server identifies JSON elements using path expressions.
Examples:
$.Name$.Address.City$.Orders[0]$.Orders[1].Price
The dollar sign ($) represents the root of the JSON document.
Understanding JSON path syntax is essential for using JSON functions correctly.
Nested JSON
Example:
{ "Customer": { "Name":"Alice", "Address": { "City":"Boston" } }}
Extracting City:
SELECTJSON_VALUE(CustomerInfo,'$.Customer.Address.City');
Working with JSON Arrays
Example JSON:
{ "Products": [ {"ID":1}, {"ID":2}, {"ID":3} ]}
Retrieve the array:
SELECTJSON_QUERY(OrderInfo,'$.Products');
Convert to rows:
SELECT *FROM OPENJSON(JSON_QUERY(OrderInfo,'$.Products'));
JSON Design Considerations
JSON is ideal when:
- Attributes vary significantly between rows.
- Schema flexibility is required.
- Data originates from APIs.
- AI systems generate dynamic metadata.
- Semi-structured information must be stored.
Relational tables are preferable when:
- Strong relationships exist.
- Frequent joins are required.
- Referential integrity must be enforced.
- Structured reporting dominates the workload.
Many production databases combine relational columns with JSON columns.
JSON and AI Applications
JSON is heavily used throughout AI-enabled database solutions.
Common examples include:
- Storing AI prompt templates
- Saving LLM responses
- Recording chat history
- Persisting vector search metadata
- Storing application configuration
- Capturing model parameters
- Logging AI inference results
Because AI services frequently communicate using JSON, SQL Server’s JSON functionality is an important integration capability.
Performance Considerations
When working with JSON:
- Validate incoming JSON using ISJSON().
- Avoid repeatedly evaluating JSON_VALUE() in large queries.
- Create computed columns for frequently queried properties.
- Index computed columns used in WHERE, JOIN, or ORDER BY clauses.
- Persist computed columns when read performance outweighs storage costs.
- Avoid storing extremely large documents unless necessary.
- Use OPENJSON() for efficient ingestion of structured JSON payloads.
Best Practices
- Store only valid JSON documents.
- Keep relational data relational whenever practical.
- Use JSON for flexible or semi-structured attributes.
- Use JSON_VALUE() for scalar values.
- Use JSON_QUERY() for arrays and objects.
- Use JSON_MODIFY() for updates.
- Use OPENJSON() to transform JSON into relational rows.
- Create computed columns for frequently searched JSON properties.
- Index computed columns to improve query performance.
- Test execution plans to verify indexes are being used.
Common Exam Tips
For the DP-800 exam, remember these key points:
- SQL Server stores JSON as NVARCHAR, not as a native JSON data type.
- ISJSON() validates JSON syntax.
- JSON_VALUE() returns a scalar value.
- JSON_QUERY() returns an object or array.
- JSON_MODIFY() updates JSON documents.
- OPENJSON() converts JSON into relational rows.
- FOR JSON AUTO and FOR JSON PATH generate JSON output from SQL queries.
- JSON properties are typically indexed by exposing them through computed columns and creating indexes on those columns.
- Persisted computed columns can improve read performance at the cost of additional storage and update overhead.
Practice Exam Questions
Question 1
A developer needs to verify that incoming API payloads contain valid JSON before storing them in a table. Which SQL Server function should be used?
A. JSON_QUERY()
B. OPENJSON()
C. JSON_VALUE()
D. ISJSON()
Answer: D
Explanation: ISJSON() validates whether a string contains properly formatted JSON. It returns 1 for valid JSON and 0 for invalid JSON, making it ideal for validation and CHECK constraints.
Question 2
A developer wants to retrieve the value of the City property from a JSON document stored in an NVARCHAR column. Which function should be used?
A. JSON_VALUE()
B. JSON_QUERY()
C. JSON_MODIFY()
D. OPENJSON()
Answer: A
Explanation: JSON_VALUE() extracts a single scalar value from a JSON document, such as a string, number, or Boolean.
Question 3
Which statement accurately describes how SQL Server stores JSON data?
A. SQL Server stores JSON in a dedicated JSON data type.
B. SQL Server stores JSON as XML internally.
C. SQL Server stores JSON as character data, typically in an NVARCHAR column.
D. SQL Server automatically converts JSON into relational columns.
Answer: C
Explanation: SQL Server does not provide a native JSON data type. JSON documents are stored as text, typically in NVARCHAR(MAX) or NVARCHAR(n) columns.
Question 4
A query frequently filters on the value returned by JSON_VALUE(CustomerInfo,'$.City'). What is the recommended way to improve query performance?
A. Create a clustered index on the JSON column.
B. Create a computed column using JSON_VALUE() and index the computed column.
C. Replace the JSON column with XML.
D. Create a foreign key on the JSON column.
Answer: B
Explanation: SQL Server cannot directly index an arbitrary JSON property. The recommended approach is to expose the property through a computed column and then create an index on that computed column.
Question 5
A developer needs to retrieve an entire JSON array from a document rather than a single scalar value. Which function should be used?
A. JSON_QUERY()
B. JSON_VALUE()
C. ISJSON()
D. JSON_MODIFY()
Answer: A
Explanation: JSON_QUERY() returns a JSON object or array, whereas JSON_VALUE() returns only scalar values.
Question 6
Which SQL Server feature converts relational query results into JSON output?
A. OPENJSON
B. JSON_VALUE
C. FOR JSON
D. JSON_MODIFY
Answer: C
Explanation: The FOR JSON clause formats SQL query results as JSON. Both AUTO and PATH modes are available.
Question 7
A developer receives a JSON document containing thousands of customer records and wants to transform it into relational rows for loading into SQL Server. Which function should be used?
A. JSON_QUERY()
B. JSON_MODIFY()
C. ISJSON()
D. OPENJSON()
Answer: D
Explanation: OPENJSON() parses JSON documents and converts them into rows and columns. It is commonly used for importing JSON data.
Question 8
What is the primary advantage of creating a persisted computed column based on a JSON property?
A. It automatically encrypts the JSON document.
B. It physically stores the computed value, reducing recalculation during queries.
C. It compresses the JSON document.
D. It converts JSON into XML.
Answer: B
Explanation: A persisted computed column stores its calculated value on disk, improving query performance by avoiding repeated evaluation of the expression.
Question 9
Which JSON path expression retrieves the City property nested inside an Address object?
A. $.City.Address
B. $.Address.City
C. $.Address[City]
D. $.City->Address
Answer: B
Explanation: JSON path expressions begin at the root ($) and navigate nested objects using dot notation, such as $.Address.City.
Question 10
Which scenario is the best candidate for storing information in a JSON column instead of creating many optional relational columns?
A. A highly normalized customer master table with strict referential integrity
B. A table containing only integer values
C. A fixed employee payroll table with mandatory attributes
D. A product catalog in which different product categories have different sets of optional attributes
Answer: D
Explanation: JSON is well suited for semi-structured or variable data, such as product attributes that differ across categories, avoiding the need for numerous nullable columns while maintaining flexibility.
Go to the DP-800 Exam Prep Hub main page
The Data Community 