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)
--> Extract language model responses
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
After a Large Language Model (LLM) generates a response, database applications must extract the returned content so it can be displayed to users, stored in the database, or used by downstream processes. Because most AI services return results in JSON format, developers must understand how to parse JSON, extract relevant values, handle errors, validate responses, and integrate the output into SQL-based applications.
For the DP-800 exam, you should understand the structure of language model responses, how to extract values using SQL JSON functions, how to handle different response formats, and the best practices for securely and efficiently processing AI-generated output.
Where Response Extraction Fits in a RAG Workflow
Extracting the language model response is one of the final stages in a Retrieval-Augmented Generation (RAG) pipeline.
User Question │ ▼Retrieve Relevant Documents │ ▼Build Prompt │ ▼Send Request to AI Model │ ▼Receive JSON Response │ ▼Extract Generated Content │ ▼Display or Store Results
Without response extraction, the application cannot effectively use the AI-generated answer.
Why AI Responses Are Returned as JSON
Most AI services expose REST APIs.
REST APIs typically exchange data using JSON because it is:
- Lightweight
- Human-readable
- Machine-readable
- Widely supported
- Easy to parse
Whether using Azure AI Foundry models, Azure OpenAI Service, or other AI providers, JSON is the standard response format.
Typical Language Model Response
Although the exact schema varies by provider and API version, chat completion APIs commonly return a structure similar to the following:
{ "choices": [ { "message": { "role": "assistant", "content": "Clustered indexes improve performance because the table rows are stored in key order." } } ]}
The application typically extracts only the generated text, while ignoring metadata unless it is needed for monitoring or diagnostics.
Common Elements in AI Responses
A language model response may include:
- Generated text
- Response identifier
- Model name
- Completion reason
- Token usage statistics
- Timestamps
- Metadata
Example (simplified):
{ "id": "chatcmpl-123", "model": "gpt-4.1", "choices": [ { "message": { "content": "Answer text..." }, "finish_reason": "stop" } ], "usage": { "prompt_tokens": 125, "completion_tokens": 38, "total_tokens": 163 }}
Developers often extract both the generated answer and token usage for logging or cost monitoring.
Parsing JSON in SQL
SQL Server and Azure SQL Database provide built-in JSON functions.
The most commonly used are:
JSON_VALUEJSON_QUERYOPENJSON
These functions enable developers to retrieve values from JSON returned by an AI service.
Using JSON_VALUE
JSON_VALUE extracts a single scalar value.
Example:
SELECT JSON_VALUE(@Response,'$.choices[0].message.content');
Result:
Clustered indexes improve performance because...
This is the most common method for retrieving the generated response.
Using JSON_QUERY
JSON_QUERY extracts JSON objects or arrays.
Example:
SELECT JSON_QUERY(@Response,'$.choices');
This returns the complete choices array rather than a single value.
Use JSON_QUERY when you need an entire object or array for further processing.
Using OPENJSON
OPENJSON converts JSON into relational rows and columns.
Example:
SELECT *FROM OPENJSON(@Response, '$.choices');
This is useful when:
- Multiple completions are returned
- Arrays must be processed
- Nested JSON must be flattened
Extracting Token Usage
Many AI services report token consumption.
Example:
{ "usage": { "prompt_tokens":125, "completion_tokens":40, "total_tokens":165 }}
Developers can extract these values.
Example:
SELECT JSON_VALUE(@Response,'$.usage.total_tokens');
Tracking token usage helps monitor:
- API costs
- Performance
- Resource consumption
Processing Multiple Choices
Some APIs may return multiple candidate responses.
Example:
{ "choices":[ {"message":{"content":"Option 1"}}, {"message":{"content":"Option 2"}} ]}
Developers can use OPENJSON to iterate through the array and select the preferred response.
Storing AI Responses
Generated responses may be:
- Displayed to users
- Saved to SQL tables
- Logged for auditing
- Indexed for future retrieval
- Used by downstream workflows
Example table:
| RequestID | UserQuestion | AIResponse | DateGenerated |
|---|
Proper storage supports auditing, analytics, and troubleshooting.
Validating Responses
Applications should validate AI responses before using them.
Check for:
- Missing content
- Empty responses
- Malformed JSON
- Unexpected schema
- API errors
Validation improves application reliability.
Handling API Errors
Not every REST call succeeds.
Possible errors include:
Authentication Failure
Examples:
- Invalid token
- Expired credentials
Network Errors
Examples:
- Timeout
- DNS failure
- Connection failure
Invalid Request
Examples:
- Malformed JSON
- Missing prompt
- Unsupported parameters
Rate Limiting
Example:
429 Too Many Requests
Applications should implement retry logic using exponential backoff where appropriate.
Finish Reasons
Many chat completion APIs include a finish reason.
Examples:
stoplengthcontent_filter
Meaning:
| Finish Reason | Description |
|---|---|
| stop | Normal completion |
| length | Maximum token limit reached |
| content_filter | Response filtered by safety system |
Applications may use this information to determine whether a response is complete.
Processing Structured Output
Some prompts request JSON output rather than plain text.
Example response:
{ "summary":"Order shipped.", "priority":"High"}
SQL JSON functions can extract each property individually.
Example:
SELECT JSON_VALUE(@Response,'$.summary');
Structured outputs are particularly useful for workflow automation.
Security Considerations
When processing AI responses:
- Validate all returned data.
- Do not assume responses are always correct.
- Avoid executing generated SQL without validation.
- Protect sensitive information.
- Log responses securely.
- Apply least-privilege access controls.
Even trusted AI services should be treated as external systems whose outputs require validation.
Performance Considerations
Large responses require:
- More network bandwidth
- More parsing time
- More storage
- More tokens
Developers should:
- Limit response length where appropriate.
- Extract only required fields.
- Avoid storing unnecessary metadata.
- Archive logs according to retention policies.
Common Mistakes
Assuming Every Response Has the Same Schema
Different AI services and API versions may return different JSON structures.
Ignoring Errors
Applications should always check for API failures before attempting to parse the response.
Parsing Entire JSON Documents
Extract only the required values to improve efficiency.
Not Validating Responses
Malformed or incomplete responses should be handled gracefully.
Ignoring Token Usage
Monitoring token consumption helps control costs.
Best Practices
- Parse responses using SQL JSON functions.
- Use
JSON_VALUEfor scalar values. - Use
JSON_QUERYfor objects and arrays. - Use
OPENJSONfor arrays and complex JSON. - Validate response schemas before processing.
- Log errors separately from successful responses.
- Track token usage for monitoring and optimization.
- Limit stored data to what is necessary.
- Handle rate limits and transient failures gracefully.
- Design applications to tolerate API schema changes when possible.
Real-World Example
A customer asks:
“Summarize this support ticket.”
The application:
- Retrieves ticket information from SQL.
- Sends it to a language model.
- Receives:
{ "choices":[ { "message":{ "content":"The customer reports intermittent login failures caused by expired authentication tokens." } } ]}
The application extracts:
SELECT JSON_VALUE(@Response,'$.choices[0].message.content');
The extracted summary is displayed to the support agent and optionally stored for future reference.
DP-800 Exam Tips
Remember these key points for the exam:
- Most language model APIs return JSON responses.
JSON_VALUEextracts individual scalar values.JSON_QUERYretrieves JSON objects or arrays.OPENJSONconverts JSON arrays and objects into relational data.- Applications should validate AI responses before using them.
- Token usage information helps monitor API costs.
- Finish reasons indicate how the model completed generation.
- Handle API errors, rate limits, and malformed responses gracefully.
- Store only the data needed for business purposes.
- AI-generated output should always be treated as data that requires validation before use.
Practice Exam Questions
Question 1
A SQL application receives a JSON response from a language model and needs to extract the generated answer.
Which SQL function is most appropriate for retrieving a single text value?
A. JSON_VALUE
B. JSON_QUERY
C. OPENJSON
D. STRING_SPLIT
Answer: A
Explanation:JSON_VALUE extracts a single scalar value from a JSON document, making it ideal for retrieving the generated response text.
Question 2
A developer wants to retrieve the entire choices array from a language model response.
Which SQL function should be used?
A. ROW_NUMBER
B. JSON_QUERY
C. MERGE
D. JSON_VALUE
Answer: B
Explanation:JSON_QUERY returns JSON objects or arrays rather than individual scalar values, making it appropriate for extracting the complete choices array.
Question 3
When is OPENJSON most useful?
A. When extracting a single property value.
B. When converting JSON arrays into relational rows and columns.
C. When generating embeddings.
D. When creating vector indexes.
Answer: B
Explanation:OPENJSON parses JSON arrays and objects into tabular data that can be queried using SQL.
Question 4
Why should applications validate AI responses before using them?
A. JSON responses are always encrypted.
B. Validation reduces database storage requirements.
C. AI responses may be malformed, incomplete, or contain unexpected structures.
D. Validation automatically reduces token usage.
Answer: C
Explanation:
Applications should verify that responses are valid, complete, and conform to the expected schema before processing them.
Question 5
A developer wants to monitor AI service costs.
Which information should be extracted from the response?
A. The database transaction log.
B. Vector dimensions.
C. Token usage statistics.
D. Query execution plans.
Answer: C
Explanation:
Many AI APIs return token usage information, which is useful for monitoring API consumption and estimating costs.
Question 6
What does a finish reason of stop typically indicate?
A. The request exceeded the maximum token limit.
B. The response was blocked by a content filter.
C. The model completed the response normally.
D. Authentication failed.
Answer: C
Explanation:
A finish reason of stop indicates that the model reached a natural completion point without interruption.
Question 7
A developer receives multiple candidate responses from an AI service.
Which SQL feature is best suited for processing all returned responses?
A. JSON_VALUE
B. OPENJSON
C. GROUP BY
D. FOR JSON AUTO
Answer: B
Explanation:OPENJSON can iterate through arrays, making it ideal for processing multiple response choices.
Question 8
Which practice best improves the reliability of applications consuming AI responses?
A. Assume every response follows the same JSON schema.
B. Execute AI-generated SQL statements without review.
C. Validate the response structure and handle errors gracefully.
D. Ignore API error messages.
Answer: C
Explanation:
Validating responses and implementing robust error handling help applications remain reliable even when API responses change or errors occur.
Question 9
Why should developers avoid storing unnecessary metadata from AI responses?
A. Metadata prevents JSON parsing.
B. It can increase storage requirements without providing business value.
C. Metadata invalidates embeddings.
D. Metadata reduces retrieval accuracy.
Answer: B
Explanation:
Storing only the required information minimizes storage costs and simplifies downstream processing.
Question 10
A SQL application receives the following JSON:
{ "choices":[ { "message":{ "content":"The shipment will arrive tomorrow." } } ]}
Which value should typically be presented to the end user?
A. The complete JSON document.
B. The choices array.
C. The generated text contained in message.content.
D. The API response identifier.
Answer: C
Explanation:
The value stored in message.content contains the natural-language response generated by the language model and is typically the information displayed to users.
Go to the DP-800 Exam Prep Hub main page
The Data Community 