Migrate to Innovate Summit:
Learn how migrating and modernizing to Azure can boost your business's performance, resilience, and security, enabling you to fully embrace AI.Register now
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Azure SQL Database, Fabric SQL database, and Azure SQL Managed Instance let you parse and query data represented in JavaScript Object Notation (JSON) format, and export your relational data as JSON text. The following JSON scenarios are available:
If you have a web service that takes data from the database layer and provides a response in JSON format, or client-side JavaScript frameworks or libraries that accept data formatted as JSON, you can format your database content as JSON directly in a SQL query. You no longer have to write application code that formats results from Azure SQL Database or Azure SQL Managed Instance as JSON, or include some JSON serialization library to convert tabular query results and then serialize objects to JSON format. Instead, you can use the FOR JSON clause to format SQL query results as JSON and use it directly in your application.
In the following example, rows from the Sales.Customer table are formatted as JSON by using the FOR JSON clause:
SQL
select CustomerName, PhoneNumber, FaxNumber
from Sales.Customers
FORJSONPATH
The FOR JSON PATH clause formats the results of the query as JSON text. Column names are used as keys, while the cell values are generated as JSON values:
The result set is formatted as a JSON array where each row is formatted as a separate JSON object.
PATH indicates that you can customize the output format of your JSON result by using dot notation in column aliases. The following query changes the name of the "CustomerName" key in the output JSON format, and puts phone and fax numbers in the "Contact" sub-object:
SQL
select CustomerName asName, PhoneNumber as [Contact.Phone], FaxNumber as [Contact.Fax]
from Sales.Customers
where CustomerID = 931FORJSONPATH, WITHOUT_ARRAY_WRAPPER
In this example, we returned a single JSON object instead of an array by specifying the WITHOUT_ARRAY_WRAPPER option. You can use this option if you know that you are returning a single object as a result of query.
The main value of the FOR JSON clause is that it lets you return complex hierarchical data from your database formatted as nested JSON objects or arrays. The following example shows how to include the rows from the Orders table that belong to the Customer as a nested array of Orders:
SQL
select CustomerName asName, PhoneNumber as Phone, FaxNumber as Fax,
Orders.OrderID, Orders.OrderDate, Orders.ExpectedDeliveryDate
from Sales.Customers Customer
join Sales.Orders Orders
on Customer.CustomerID = Orders.CustomerID
where Customer.CustomerID = 931FORJSONAUTO, WITHOUT_ARRAY_WRAPPER
Instead of sending separate queries to get Customer data and then to fetch a list of related Orders, you can get all the necessary data with a single query, as shown in the following sample output:
If you don't have strictly structured data, if you have complex sub-objects, arrays, or hierarchical data, or if your data structures evolve over time, the JSON format can help you to represent any complex data structure.
JSON is a textual format that can be used like any other string type. You can send or store JSON data as a standard NVARCHAR:
SQL
CREATETABLE Products (
Idintidentity primary key,
Title nvarchar(200),
Datanvarchar(max)
)
goCREATEPROCEDURE InsertProduct(@title nvarchar(200), @jsonnvarchar(max))
ASBEGINinsertinto Products(Title, Data)
values(@title, @json)
END
The JSON data used in this example is represented by using the NVARCHAR(MAX) type. JSON can be inserted into this table or provided as an argument of the stored procedure using standard Transact-SQL syntax as shown in the following example:
Any client-side language or library that works with string data will also work with JSON data. JSON can be stored in any table that supports the nvarchar type, such as a Memory-optimized table or a System-versioned table. JSON does not introduce any constraint either in the client-side code or in the database layer.
Querying JSON data
If you have data formatted as JSON stored in Azure SQL tables, JSON functions let you use this data in any SQL query.
JSON functions let you treat data formatted as JSON as any other SQL data type. You can easily extract values from the JSON text, and use JSON data in any query:
The JSON_VALUE function extracts a value from JSON text stored in the Data column. This function uses a JavaScript-like path to reference a value in JSON text to extract. The extracted value can be used in any part of SQL query.
The JSON_QUERY function is similar to JSON_VALUE. Unlike JSON_VALUE, this function extracts complex sub-object such as arrays or objects that are placed in JSON text.
The JSON_MODIFY function lets you specify the path of the value in the JSON text that should be updated, as well as a new value that will overwrite the old one. This way you can easily update JSON text without reparsing the entire structure.
Since JSON is stored in a standard text, there are no guarantees that the values stored in text columns are properly formatted. You can verify that text stored in JSON column is properly formatted by using standard check constraints and the ISJSON function:
SQL
ALTERTABLE Products
ADDCONSTRAINT [Data should be formatted asJSON]
CHECK (ISJSON(Data) > 0)
If the input text is properly formatted JSON, the ISJSON function returns the value 1. On every insert or update of JSON column, this constraint will verify that new text value is not malformed JSON.
Transforming JSON into tabular format
You can transform JSON collections into tabular format and load or query JSON data.
OPENJSON is a table-value function that parses JSON text, locates an array of JSON objects, iterates through the elements of the array, and returns one row in the output result for each element of the array.
In the example above, we can specify where to locate the JSON array that should be opened (in the $.Orders path), what columns should be returned as result, and where to find the JSON values that will be returned as cells.
We can transform a JSON array in the @orders variable into a set of rows, analyze this result set, or insert rows into a standard table:
SQL
CREATEPROCEDURE InsertOrders(@orders nvarchar(max))
ASBEGINinsertinto Orders(Number, Date, Customer, Quantity)
selectNumber, Date, Customer, Quantity
FROM OPENJSON (@orders)
WITH (
Numbervarchar(200),
Date datetime,
Customer varchar(200),
Quantity int
)
END
The collection of orders formatted as a JSON array and provided as a parameter to the stored procedure can be parsed and inserted into the Orders table.
Learn how to invoke REST endpoints in Azure SQL Database and manipulate data using Azure Functions. Also, explore various tools and options for importing and exporting data to and from Azure SQL Database.
In this episode of Data Exposed with Anna Hoffman and Umachandar Jayachandran, you will learn how to use JSON documents from Transact-SQL in Azure SQL Database and SQL Server 2022. We will look at how JSON documents can be stored in tables, modified and queried in the database. We will also look at how to transform relational data into JSON and vice versa. Chapters 00:25 - Introduction 00:45 - JSON in SQL Server 01:39 - Demo of storing and querying JSON in SQL Server 03:00 - Changing a JSON Document 04:10
The native JSON type allows JSON documents to be stored in a binary format. This binary format allows for efficient query processing and in-place modifications of JSON documents. The JSON aggregates - JSON_OBJECTAGG & JSON_ARRAYAGG enable easy aggregation of relational data into a JSON document. Learn more in this episode of Data Exposed with Anna Hoffman and Umachandar Jayachandran. Chapters 00:00 - Introduction 01:46 - Demo 04:00 - Getting started Recommended resources JSON data type (preview) - SQ