JSON (JavaScript Object Notation) is the most widely used data exchange formats that use text value or attributes for representing the entire data. Moreover, it provides a data transmission technology that is much easier to read and understand. Also, the SQL server 2016 gives the built-in functions for JSON support for importing, exporting, processing and parsing the JSON text. The main purpose is to create a simplified and useful framework for processing JSON documents. In the following section, we will cover the brief discussion of JSON features in SQL Server and more in detail.
SQL Server 2016 JSON Support
There are following JSON features that are planned in SQL Server:
- SQL Server 2016 CTP2:
- SQL Server 2016 CTP3:
Ability to export and format the data in JSON document.
Ability to extract the values from JSON text and then, load the JSON text into tables, indexing properties in JSON text saved in columns and so on.
NOTE: First, we must be aware of the built-in JSON support in SQL Server that is not same as the native JSON type.
Representation of JSON Data
In SQL Server 2016, JSON will be represented as NVARCHAR type. Let us discuss the below reasons such as:
- JSON Migration: We observed that most of the people already store the JSON as text, so there is a need to change the database schema and reload data for using new features in case we are introducing a separate JSON type. According to the implementation, developers able to use the JSON functionalities without any modification in the database.
- Cross Feature Compatibility: Generally, JSON text is stored in VARCHAR or NVARCHAR columns and indexed in plain text. Moreover, NVARCHAR type is supported for all the SQL components, therefore JSON will be supported everywhere.
- Client-Side Support: Recently, we do not have standardized JSON type object in client-side applications. Especially, JavaScript clients will use JSON text and also parse it with their native parsers.
Export SQL Data as JSON with FOR JSON
Earlier, we have mentioned the feature that will be available in Microsoft SQL server 2016 CTP2 to format the query results as JSON text by using JSON clause. If users are well-known with FOR XML clause then, they can easily understand FOR JSON:
When a user wants to add FOR JSON clause in the ending of T-SQL SELECT query, SQL server will take the outputs and format them in the form of JSON text and return it to the client. Each row will get formatted as JSON object, values will be created as values of JSON objects and also column names will be used as a key name. Here, we have two type of FOR JSON clauses in SQL server 2016 those are mentioned:
- FOR JSON PATH that allows a user for defining the structure of output JSON by using the column names or aliases. In case, if a user can put dot-separated names within the column aliases then, JSON properties might follow the naming patterns.
- FOR JSON AUTO mode that creates nested JSON sub-arrays automatically, which is based on the particular table hierarchy and also, common to FOR XML AUTO.
This feature must be similar to FOR XML PATH where users able to use slash-separated paths.
Moreover, the main objective of FOR JSON is to generate the JSON based variables and columns and format it as per the specification of JSON clause.
For example:
Transform JSON text to relational table using OPENJSON
OPENJSON is a table-value function, which seeks into JSON text, locate an array of the JSON objects, iterate them via an element of the array and generate one row for each element in the desired result. JSON text is created with FOR JSON clause and able to transform back into the relational form by using OPENJSON. Let us discuss the following types of OPENJSON function:
- OPENJSON with predefined output schema that will allow to define the schema of the table that might be returned, and mapping rules that might be specified what the properties will be represented by the returned columns.
- OPENJSON function, without return schema where the result of the table-value function would be set of key-pairs.
An Example of OPENJSON function in Transaction-SQL query that will show in the below example:
@OrderDetails is a text variable that consists an array of JSON objects within the Property OrdersArray as it is considered in the below example:
'{"OrdersArray":[
{"Number":2, "Date":"10/1/2016","Customer":"Adventure works","Quantity": 1100},
{"Number":5, "Date":"9/11/2016","Customer":"Adventure works","Quantity": 1600},
{"Number":8,"Date":"22/8/2016","Customer":"Adventureworks","Quantity": 2000},
{"Number":9,"Date":"10/10/2016","Customer":"Adventureworks","Quantity": 2100}
]}'
OPENJSON function will find an array in this property, which returns two rows for each JSON object. Five columns in the set of the result are defined in WITH clause. OPENJSON wants to find out the properties Number, Date, Customer and Quantity in each JSON and convert their each value into columns result set. Null value returns the property as not found by default. This is the easiest example, but OPENJSON object is available for parsing different file formats of JSON text or even for nested JSON objects.
Built-in functions available for processing JSON text
We are providing set of useful functions for processing as well as parsing JSON text. Moreover, JSON built-in functions that will be present in SQL Server 2016 CTP3 such as:
- ISJSON i.e. JSON text that verifies the NVARCHAR text input formatted properly according to the specification of JSON text. Also, this function is used to create check constraints on NVARCHAR columns containing JSON text.
- JSON-VALUE i.e. JSON Text that processes the JSON Text and need to extract scalar value on the particular Javascript-path.
Let us discuss the syntax of the path that will be utilized in built-in functions. Those are mentioned below:
- '$' signifies the entire JSON object within the input text
- '$.property1' refers the property1 in the JSON object
- '$[5]' indicates the 5th element in the JSON array
- '$.property1.property2.array1[15].property4' refers the complex nested property inside the JSON object.
Conclusion
JSON is an open standard format that can easily be used for storing and exchanging the data. Additionally, users must keep in mind that earlier version of SQL Server does not provide a built-in JSON data type, however, SQL server 2016 supports JSON. Also, we have discussed the two JSON features in detail for SQL Server 2016 in order to have a better understanding.
EmoticonEmoticon