Introduction
JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is language-independent, easy to understand, and self-describing. It is used as an alternative to XML. JSON is a trendy data interchange format nowadays. Most modern services return the data in JSON text. SQL Server JSON is one of the needs for data developers to return JSON in SQL Server
The Built-in JSON support in the SQL server is different from the native JSON type. JSON will be represented as an NVARCHAR type for the following reasons..
1- Cross feature compatibility
The data type NVARCHAR has supported all the SQL server components such as Hekaton, temporal, column store tables, etc. It works with almost all the features of SQL Server. If we think JSON works with the X feature of SQL Server, the simple answer is that if NVARCHAR works with the X feature, JSON will also work.
2- Migration
Before SQL Server, developers stored JSON in the database as text. They needed to change the database schema and migrate the data into a new feature if JSON type was introduced.
3- Client-side support
Currently, there is no standardized JSON object type on the client side, such as an XmlDom object. JSON is treated as Object in JavaScript.
The following in-built functions are introduced in SQL Server to support JSON.
- ISJSON
- JSON_VALUE
- JSON_QUERY
- JSON_MODIFY
- OPENJSON
- FOR JSON
ISJSON (json string)
This function is very useful for checking the input string in JSON format before it stores in the database. It checks whether the supplied NVARCHAR text input is in the proper format according to JSON specification. This function returns an INT value; if the string is correctly formatted as JSON, it returns 1. Else it returns 0
Lets understand with below given example.
JSON_VALUE (json string, path)
It returns a scalar value from a JSON string. It parses JSON string and extracts scalar value from JSON string by a specific path. There is some specific format for providing the path. For example
- '$' - reference entire JSON object
- '$.Property1' - reference property1 in JSON object
- '$[2]' - reference 2nd element in JSON array
- '$.Property1.property2[4].property3' - reference nested property in JSON object
Lets understand with given below Examle.
It returns null if the specified path is not found in the JSON object. If we want to throw the error if the specified path is not found in the JSON object, we can use the 'strict' keyword before the path.
JSON_QUERY(json string, path)
It extracts an array of data or objects from the JSON string. In the following example, I have extracted "Addresses" data from the JSON object and the first element of "Addresses" data from the JSON object.
If the JSON string contains the duplicate property, i.e., two keys with the same name and on the same level, JSON_VALUE and JSON_QUERY functions return the first value that matches the path.
JSON_MODIFY (json string, path, new value)
This function returns an updated JSON string in NVARCHAR type. It takes three parameters; the first parameter is the JSON string, the second parameter is a path on which the value needs to change, and the third parameter is the value that needs to update. Using this function, we can insert, update, delete, or append a value to the JSON string.
Update existing value.
To update the value of the existing JSON, we need to provide the exact path with the new value. For example, we can update the value of the FirstName field of the JSON string using the following query.
Multiple updates
Using the function JSON_MODIFY, we can update only one property; if we want to update multiple properties, we need to use multiple JSON_MODIFY calls.
Deleting existing value
To delete an existing value, we need to provide the full path of the element and set a new value to NULL.
OPENJSON
A table value function will generate a relational table with its contents from the JSON string. It will iterate through JSON object elements and arrays and generate a row for each element. We can generate a table without a pre-defined schema or a well-defined schema
This functionality will return the value as key-value pairs, including their type. The following example shows JSON data as key-value pair with its type.
OPENJSON with a Pre-defined Schema
OPENJSON function can also generate a result set with a pre-defined schema. If we generate results with a pre-defined schema, it generates a table based on provided schema instead of key-value pair.
We can also access child JSON objects as well using the OPENJSON function. This can be done by CROSS APPLYing the JSON child element with the parent element.
In the following example, the EmployeeInfo and Addresses objects are fetched and applied to Cross join on. We need to use the "AS JSON" option in the column definition to specify which references the property that contains the child JSON node. In the column specified with the "AS JSON" option, the type must be NVARCHAR (MAX). Without this option, this function returns a NULL value instead of a child JSON object and returns a run time error in "strict" mode.
FOR JSON
Function FOR JSON is very useful when exporting SQL table data in JSON format. It is very similar to FOR XML function. Here, column names or aliases are key names for JSON objects. There are two options FOR JSON.
- AUTO - It will create nested JSON sub-array based on the table hierarchy used in the query.
- PATH - It enables us to define the required JSON structure using the column name or aliases. If we put dot (.) separated names in the column aliases, JSON properties follow the same naming convention.
The FOR JSON AUTO is suitable for most scenarios, but FOR JSON PATH is very useful in specific scenarios where we must control how JSON data is generated or nested. The FOR JSON PATH gives us full control to specify the output format for JSON data.
Convert Table to JSON Format
