This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Implement AI capabilities in database solutions (25–30%)
   --> Design and implement retrieval-augmented generation (RAG)
      --> Convert structured data to JSON for language model processing

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

Modern AI-enabled database applications frequently need to send structured data stored in relational tables to Large Language Models (LLMs). Because LLMs interact with text or structured payloads such as JSON rather than relational tables, developers must transform SQL query results into JSON before sending them to AI services.

For the DP-800 exam, you should understand how to convert relational data into JSON using SQL, why JSON is the preferred interchange format for AI services, how JSON is used in Retrieval-Augmented Generation (RAG) workflows, and the best practices for preparing structured data for language model processing.

Why Convert Structured Data to JSON?

Relational databases organize information into:

• Tables
• Rows
• Columns
• Relationships

Large Language Models, however, consume:

• Natural language
• JSON documents
• API payloads
• Structured text

JSON (JavaScript Object Notation) provides a lightweight, hierarchical format that is easy for applications, APIs, and AI models to process.

Instead of sending an entire table, developers typically send only the relevant records formatted as JSON.

Role of JSON in AI Applications

JSON serves as the common data exchange format between SQL databases and AI services.

Typical workflow:

SQL Database
      │
      ▼
Query Structured Data
      │
      ▼
Convert to JSON
      │
      ▼
Build AI Prompt
      │
      ▼
REST API Request
      │
      ▼
Large Language Model
      │
      ▼
AI Response

This process allows structured business data to become part of an AI prompt or API request.

What Is JSON?

JSON is a text-based format consisting of key-value pairs and arrays.

Example:

{
  "CustomerID": 1001,
  "CustomerName": "Contoso Ltd.",
  "Country": "USA",
  "CreditLimit": 50000
}

Nested objects are also supported.

Example:

{
  "OrderID": 1055,
  "Customer": {
      "Name": "Contoso Ltd.",
      "Country": "USA"
  }
}

Hierarchical structures like these are easier for language models to interpret than tabular data.

Why AI Models Prefer JSON

JSON provides several advantages:

• Human-readable
• Machine-readable
• Structured
• Flexible
• Widely supported
• Easily serialized
• Easily parsed

Most AI REST APIs accept JSON request bodies and return JSON responses.

Converting SQL Query Results to JSON

Modern SQL platforms support generating JSON directly from query results.

For example, SQL Server and Azure SQL Database provide the FOR JSON clause.

Example:

SELECT CustomerID,
       CustomerName,
       Country
FROM Customers
FOR JSON AUTO;

Sample output:

[
  {
    "CustomerID":1001,
    "CustomerName":"Contoso Ltd.",
    "Country":"USA"
  },
  {
    "CustomerID":1002,
    "CustomerName":"Fabrikam",
    "Country":"Canada"
  }
]

This JSON can be incorporated into prompts or REST API requests.

FOR JSON AUTO

FOR JSON AUTO automatically generates JSON based on the structure of the SELECT statement.

Advantages:

• Minimal configuration
• Quick generation
• Good for simple queries

Example:

SELECT ProductID,
       ProductName,
       Price
FROM Products
FOR JSON AUTO;

FOR JSON PATH

FOR JSON PATH provides greater control over the resulting JSON structure.

Example:

SELECT
    CustomerID AS 'Customer.ID',
    CustomerName AS 'Customer.Name'
FOR JSON PATH;

Output:

[
  {
    "Customer": {
      "ID":1001,
      "Name":"Contoso Ltd."
    }
  }
]

FOR JSON PATH is preferred when a specific JSON schema is required by an application or AI service.

Creating Nested JSON

Nested JSON is useful for representing parent-child relationships.

Example:

Customer

↓

Orders

↓

Order Items

Instead of returning multiple unrelated tables, developers can build a hierarchical JSON document that mirrors the business object.

This format is often easier for an LLM to understand.

Using JSON in Prompts

Rather than embedding raw SQL results, developers can include JSON as structured context.

Example prompt:

Use the following customer information:
{
  "CustomerID":1001,
  "Name":"Contoso Ltd.",
  "Country":"USA",
  "CreditLimit":50000
}
Summarize the customer's profile.

The structured format enables the model to identify fields and values more reliably.

JSON in Retrieval-Augmented Generation (RAG)

In RAG applications, retrieved information often comes from:

• SQL queries
• Vector search
• Hybrid search
• APIs

Structured query results can be converted to JSON before being added to the prompt.

Workflow:

SQL Query
      │
      ▼
FOR JSON
      │
      ▼
Prompt Construction
      │
      ▼
LLM
      │
      ▼
Grounded Response

Combining Structured and Unstructured Data

Many AI applications combine relational data with documents.

Example:

Structured data:

{
  "OrderID":1055,
  "Status":"Shipped"
}

Retrieved documentation:

Orders typically arrive within three business days after shipment.

Prompt:

Order Information:
{
  "OrderID":1055,
  "Status":"Shipped"
}
Documentation:
Orders typically arrive within three business days.
Answer the customer's question.

This approach gives the LLM access to both factual business data and supporting context.

Reducing Token Usage

Large JSON payloads increase:

• Prompt size
• Latency
• API cost
• Token consumption

Best practice:

Include only relevant fields.

Instead of:

{
  "CustomerID":1001,
  "Name":"Contoso",
  "Country":"USA",
  "Phone":"...",
  "Fax":"...",
  "CreatedDate":"...",
  "LastLogin":"...",
  ...
}

Use:

{
  "CustomerID":1001,
  "Country":"USA",
  "CreditLimit":50000
}

Only include information required to answer the user’s question.

Security Considerations

Before converting SQL data to JSON:

• Remove sensitive columns.
• Exclude personally identifiable information (PII) unless required and authorized.
• Apply row-level security (RLS).
• Enforce column-level permissions.
• Mask confidential values when appropriate.
• Validate user authorization before retrieving data.

AI models should receive only the data necessary to perform the requested task.

Data Quality Considerations

Language model responses are only as good as the input data.

Ensure that:

• Missing values are handled appropriately.
• Duplicate rows are removed.
• Invalid records are excluded.
• Data types are consistent.
• Field names are meaningful.
• JSON is well-formed and valid.

Poor-quality JSON often leads to inaccurate or confusing AI responses.

Processing AI Responses

Most AI services also return JSON.

Example:

{
  "summary":
  "Contoso Ltd. is a U.S. customer with a credit limit of $50,000."
}

SQL JSON functions such as:

• JSON_VALUE
• JSON_QUERY
• OPENJSON

can extract values from the response for further processing or storage.

Common Mistakes

Sending Entire Tables

Avoid sending unnecessary rows.

Instead:

Retrieve only relevant records.

Including Too Many Columns

Large prompts increase token usage and cost.

Using Poor Field Names

Prefer:

CustomerName

instead of:

C_Name

Clear field names help improve model understanding.

Ignoring Security

Never expose confidential information unnecessarily.

Creating Invalid JSON

Malformed JSON causes REST API failures and prevents AI services from processing requests.

Best Practices

• Use FOR JSON AUTO for simple JSON generation.
• Use FOR JSON PATH when custom JSON structures are required.
• Return only relevant rows and columns.
• Keep JSON concise to reduce token consumption.
• Use meaningful field names.
• Remove confidential or unnecessary information.
• Validate JSON before sending it to AI services.
• Combine structured JSON with retrieved documents for RAG scenarios.
• Parse AI responses using SQL JSON functions.
• Test prompts using realistic business data.

DP-800 Exam Tips

Remember these key points for the exam:

• JSON is the standard format for exchanging structured data with AI services.
• SQL Server and Azure SQL Database support JSON generation using FOR JSON.
• FOR JSON AUTO automatically formats query results.
• FOR JSON PATH provides greater control over JSON structure.
• RAG solutions often include JSON generated from SQL queries as contextual information.
• Smaller, focused JSON payloads reduce token usage and improve performance.
• Protect sensitive information before converting data to JSON.
• SQL JSON functions can parse AI responses returned as JSON.

Practice Exam Questions

Question 1

A database developer needs to send customer records from SQL Server to a Large Language Model through a REST API.

Which format is most appropriate?

A. XML

B. CSV

C. JSON

D. Binary data

Answer: C

Explanation:
JSON is the standard format accepted by most AI REST APIs because it is lightweight, structured, and easy for both applications and language models to process.

Question 2

Which SQL clause automatically converts query results into JSON using the default structure of the SELECT statement?

A. FOR JSON AUTO

B. FOR XML

C. OPENJSON

D. JSON_VALUE

Answer: A

Explanation:
FOR JSON AUTO automatically generates JSON based on the query structure with minimal configuration.

Question 3

A developer needs complete control over the hierarchy and property names in the generated JSON document.

Which SQL feature should be used?

A. FOR XML

B. FOR JSON PATH

C. JSON_QUERY

D. OPENJSON

Answer: B

Explanation:
FOR JSON PATH allows developers to customize the JSON structure, including nested objects and property names.

Question 4

Why is JSON commonly used when interacting with Large Language Models?

A. It permanently stores embeddings.

B. It replaces vector indexes.

C. It provides a structured, machine-readable format that AI services commonly accept.

D. It automatically encrypts database records.

Answer: C

Explanation:
JSON is widely supported by REST APIs and AI services, making it the preferred format for exchanging structured data.

Question 5

In a Retrieval-Augmented Generation (RAG) solution, why might structured SQL query results be converted to JSON?

A. To include structured business data as context in the prompt sent to the language model.

B. To train the language model.

C. To replace vector embeddings.

D. To eliminate REST APIs.

Answer: A

Explanation:
Structured SQL data converted to JSON can be included in the prompt, allowing the LLM to generate grounded responses using current business information.

Question 6

A developer includes every column from a customer table in the JSON payload, even though only two fields are required.

What is the most likely consequence?

A. Improved retrieval accuracy.

B. Lower API costs.

C. Increased prompt size, token consumption, and latency.

D. Automatic JSON compression.

Answer: C

Explanation:
Sending unnecessary data increases the size of the prompt, which leads to higher token usage, longer response times, and increased cost.

Question 7

Which SQL functions are commonly used to extract values from a JSON response returned by an AI service?

A. ROW_NUMBER and MERGE

B. JSON_VALUE, JSON_QUERY, and OPENJSON

C. PIVOT and UNPIVOT

D. STRING_AGG and GROUP BY

Answer: B

Explanation:
SQL Server provides JSON functions such as JSON_VALUE, JSON_QUERY, and OPENJSON for parsing JSON documents and extracting data.

Question 8

Which practice best improves both security and efficiency when preparing JSON for an AI service?

A. Include every available database column.

B. Return the entire table regardless of the user’s request.

C. Remove unnecessary and sensitive information before generating JSON.

D. Convert the JSON into XML before sending it.

Answer: C

Explanation:
Limiting the JSON payload to only necessary, authorized data reduces token usage, improves performance, and protects sensitive information.

Question 9

What is the primary advantage of using nested JSON structures?

A. They reduce the need for SQL joins.

B. They represent hierarchical relationships in a format that is easier for applications and language models to interpret.

C. They automatically generate embeddings.

D. They eliminate the need for REST APIs.

Answer: B

Explanation:
Nested JSON naturally represents parent-child relationships, making complex business objects easier for both applications and AI models to process.

Question 10

A database application receives a JSON response from an AI service.

What is the next step if the application needs to store the generated summary in a SQL table?

A. Convert the JSON to XML.

B. Rebuild the vector index.

C. Parse the JSON response using SQL JSON functions and extract the required value.

D. Generate new embeddings for the response.

Answer: C

Explanation:
After receiving a JSON response, SQL functions such as JSON_VALUE or OPENJSON can extract the generated content for storage or further processing.

Go to the DP-800 Exam Prep Hub main page