SQL Server 2016 provides the built-in support for importing, exporting, parsing, and querying JSON documents or fragments. In this article, we will discuss this feature in more details!
JSON (JavaScript Object Notation) is a lightweight data-interchange format that uses text name/value (or attribute/value) pairs to represent data. JSON serves as a data transmission technology that is easier to read and is less complicated than XML.
Some NoSQL engines, including Azure DocumentDB, use JSON as the storage format. DocumentDB uses Azure Blob storage to store schema-less documents but provides a rich SQL query dialect that allows you to conduct SQL queries over the data contained in the documents.
Azure Search also utilizes JSON. Azure Search is a fully managed search solution that allows developers to embed sophisticated search experiences into Web and mobile applications without having to worry about the complexities of full-text search and without having to deploy, maintain or manage any infrastructure.
The combination of SQL Server’s new support for JSON with these other Microsoft tools enables many scenarios for moving data back and forth between relational and schema-less storage and the applications that access such data.
Note, SQL Server does not provide a built-in JSON data type. JSON text is typically stored in varchar or nvarchar columns and is indexed as plain text.
JSON Data
Convert JSON to rows and columns or import JSON
You can call the OPENJSON rowset function to format JSON data as rows and columns. You can also use OPENJSON to import JSON data into SQL Server, or to convert JSON data into rows and columns for an application or service that cannot currently accept JSON directly.
Convert SQL Server data to JSON or export JSON
You can format SQL Server data or query result as JSON. For this, add the FOR JSON clause to a SELECT statement. You can also use FOR JSON to delegate the formatting of JSON output from your client applications to SQL Server.
Format Query Results as JSON with FOR JSON
While using the FOR JSON clause, you can specify the output structure explicitly, or let the structure of the SELECT statement determine the output.
FOR JSON clause: PATH mode
You can use the PATH mode with the FOR JSON clause to maintain full control over the format of the JSON output. You can create wrapper objects and nest complex properties.
In this mode, you can use the dot syntax. For example, ‘Item.Price’ to format nested output. This following example also uses the ROOT option to specify a named root element.
FOR JSON clause: AUTO mode
You can use the AUTO mode with the FOR JSON clause. In this case, the JSON output is formatted automatically based on the structure of the SELECT statement.
In this mode, the structure of the SELECT statement determines the format of the JSON output. By default, null values are not included in the output. You can use the INCLUDE_NULL_VALUES to change this behavior.
FOR JSON Options
You can control the output of the FOR JSON clause by using the following options:
- Specify the ROOT option to add a single, top-level element to the JSON output. If you do not specify this option, the JSON output will not have a root element.
- Specify the INCLUDE_NULL_VALUES option to include null values in the JSON output. If you do not specify this option, the output will not include JSON properties for NULL values in the query results.
- Specify the WITHOUT_ARRAY_WRAPPER option to remove the square brackets that surround the JSON output of the FOR JSON clause by default. Otherwise, the JSON output will be enclosed within square brackets.
FOR JSON Clause Output
1. The result set contains a single column. A small result set may contain a single row. A large result set contains multiple rows.
2. While selecting rows from a table, the result set is formatted as an array of JSON objects.
- The number of elements in the array is equal to the number of rows in the results.
- Each row in the result set becomes a separate JSON object in the array.
- Each column in the result set becomes a property of the JSON object.
3. Names of columns and their values are escaped according to JSON syntax.
Convert JSON Data to Rows and Columns with OPENJSON
You can import JSON data into SQL Server, or convert JSON data to rows and columns for an application or service that cannot currently consume JSON directly, by using the OPENJSON rowset function.
You can use OPENJSON clause with or without an explicit schema that defines the structure of the output.
When you use OPENJSON with the default schema, the function returns a table with one row for each property of the object or for each element in the array.
SELECT * FROM OPENJSON('{"name":"John","surname":"Doe","age":45}')
When you use OPENJSON with an explicit schema, the function returns a table with the schema that you define in the WITH clause. In the WITH clause, you specify the output columns, their types, and the paths of the source properties for each column.
In the following example, for each element in the array in the input expression, OPENJSON generates a separate row in the output table. For each property of the array elements specified by using the colName type column_path syntax, OPENJSON converts the value to the specified type and populates a cell in the output table.
SET @json = N'{"Orders":
{"OrdersArray":
[
{
"Order": {
"Number":"SO43659",
"Date":"2011-05-31T00:00:00"
},
"AccountNumber":"AW29825",
"Item": {
"Price":2024.9940,
"Quantity":1
}
},
{
"Order": {
"Number":"SO43661",
"Date":"2011-06-01T00:00:00"
},
"AccountNumber":"AW73565",
"Item": {
"Price":2024.9940,
"Quantity":3
}
}
]
}
}'
SELECT * FROM
OPENJSON ( @json, '$.Orders.OrdersArray' )
WITH (
Number varchar(200) '$.Order.Number' ,
Date datetime '$.Order.Date',
Customer varchar(200) '$.AccountNumber',
Quantity int '$.Item.Quantity'
)
Results:
