4 min read
We are happy to announce that public preview period of new JSON functionalities in Azure SQL Database has ended in August 2016. JSON functionalities are now generally available in all service tiers of Azure Sql Database.
With new JSON functionalities, Azure SQL Database enables you to easily integrate your database with other services that communicate using JSON documents, and to handle variety of data that should be stored in your database.
JSON is popular format used for data exchange between various modern applications and services. It is also used for storing semi-structured data.
When to consider JSON with Azure SQL Database?
Azure SQL Database provides a hybrid model for storing and querying relational and JSON data. You can use standard relational models to organize your data, add flexibility in your schema using JSON, and use full SQL language for querying both relational and JSON columns.
JSON functionalities in Azure SQL Database can help you in different scenarios.
Easy ingestion of JSON data
If you are working with systems and devices that provide data formatted as JSON (e.g. mobile devices, sensors, Azure Stream Analytics, or Application Insight), you can directly send data to Azure SQL Database without any additional layer that transforms JSON to relational format. Raw JSON documents can be directly sent to Azure SQL Database where they can be stored in original format or converted to the relational model.
Exchange data with other systems or libraries via JSON documents
Web frameworks and libraries such as JQuery, D3, AngularJS, or ReactJS, that read server data via AJAX calls; REST web services that provide data in JSON format, and components that show data formatted as GeoJSON may directly use data from Azure SQL Database formatted as JSON. Without need for additional components to transform data from Azure SQL Database into JSON format, you can easily format data stored in Azure SQL Database as JSON documents that can be directly used in these libraries and services.
Handling data variety with flexible data models
Do you need to add semi-structured data in your relational models? If you have data models with a large number of different properties that might frequently change, such as catalogs with different product types, questionnaires and survey results with various questions, sensor data with information that depend on sensor type and purpose, you might need some flexibility to store variable parts of data. These data models require relationships between entities, but also flexibility for easily adding and changing fields and data structures. By combining relational models and JSON fields, you can easily design the best model for these use cases and query both relational and JSON columns.
What is it available?
Azure SQL Database works with JSON documents in standard well-known textual format. There is no need to convert your JSON documents to some custom binary serialization format or type, or to reload or transform your JSON data. Overview of JSON functionalities available in Azure SQL Database is shown on the following figure:
Azure SQL Database provides simple built-in functions that enable you to extract value from JSON text and use it in queries (JSON_VALUE), extract objects from JSON (JSON_QUERY), update some value in JSON text (JSON_MODIFY) and verify that JSON text is properly formatted (ISJSON). These four, simple, easy to learn functions can be used in any SQL query.
Without need to learn new JSON query language you can query both JSON and relational columns in the same queries using well-known standard SQL language and new built-in JSON functions. Applications and tools would not see any difference between values taken from table columns and the values extracted from JSON text. New OPENJSON function enables you to convert JSON text into table structure.
Transformed JSON can be queried using standard SQL language or loaded into relational tables. Finally, JSON functionalities enable you to easily format database content and results of SQL queries as JSON text using FOR JSON clause. Formatted JSON can be used in any application or service that understands JSON.
To learn how to integrate JSON in your application, check out TechNet Blog, MSDN documentation or Channel 9 video. To learn about various scenarios that show how to integrate JSON in your application, see demos in this Channel 9 video or find some scenario that might be interesting for your use case in JSON Blog posts.
Try out JSON functions today – we are eager to hear your feedback!