All 7 JSON Support in SQL Server questions
More in JSON Support in SQL Server
How do you store and query JSON formatted text in SQL Server? How do you convert a JSON array stored in a column into a relational table format that can be queried like normal rows? How do you generate JSON formatted output directly from a SQL query's result set? How do you update a specific value within a JSON document stored in a column without replacing the entire JSON text? How would you validate that a value stored in a column actually contains well formed JSON before allowing it to be inserted or updated? What are the performance considerations and limitations of querying JSON data stored as text compared to using normalized relational columns?
Advertisement
Back to results for “SQL Queries”
SQL Server › JSON Support in SQL Server

What is the difference between the JSON_VALUE and JSON_QUERY functions?

Beginner

Answer

JSON_VALUE extracts a single scalar value, like a string or number, from a JSON document, while JSON_QUERY extracts an entire object or array from within the JSON, letting you pull out either a simple individual value or a more complex nested structure depending on which function you use.
SELECT JSON_VALUE(OrderData, '$.customerName') AS CustomerName,
       JSON_QUERY(OrderData, '$.items') AS ItemsArray
FROM Orders;
Real-world example An order processing system extracts the customer's name as a simple text value using JSON_VALUE, while extracting the entire list of ordered items as a nested JSON array using JSON_QUERY for further processing.

Common follow-ups: What happens if you try to use JSON_VALUE on a JSON object or array instead of a scalar value?;Can JSON_QUERY be used to extract a single object from within an array?

Data Types & Schema Design;Subqueries
All 7 JSON Support in SQL Server questions