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.
Why JSON?
JSON is popular format used for data exchange between various modern applications and services. It is also used for storing semi-structured data.
JSON can be found everywhere in Azure ecosystem. JSON documents are produced by web and mobile clients (e.g. JavaScript libraries in browsers), sensors, and services that generate logs (e.g. Application Insight). They can be processed using various Azure services such as Azure Stream Analytics, Azure Data Factory, or Azure Machine Learning. Finally, JSON documents can be stored in Azure DocumentDB, Azure Blob or Table Storage, Azure Data Lake, or Azure SQL Database. Azure Search can also index JSON document stored in various data sources.
Azure SQL Database enables you to store JSON in standard textual format, use standard SQL language for querying JSON data, with the same indexing methods as on other standard SQL data types. If you need a specialized JSON database in order to take advantage of automatic indexing of JSON fields, tunable consistency levels for globally distributed data, and JavaScript integration, you may want to choose Azure DocumentDB as a storage engine.
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.
Next steps
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!