JSON in SQL Server 2016

JSON functions in SQL Server enable the Analyze and query JSON data, transform JSON to relational format, and export SQ L query results as JSON text.

If you have the JSON text, you can extract the data from JSON or verify. JSON is properly formatted using built-in functions J Son_value, Json_query, and Isjson. For more advanced querying and analysis, the Openjson function can transform an array of JSON objects into a set of rows. Any SQL query can is executed on the returned result set. Finally, there is the-JSON clause that enables-to format query results as JSON text.

SELECT TOP [Version]      , [OSType]      , [Online]      , [onlinet] from  [applicationtest].[ DBO]. [Table1]  For JSON auto

　　

We can start with simple examples. In the following Transact-SQL code, we'll define a text variable where we'll put JSON text:

DECLARE @json NVARCHAR (4000) SET @json = N ' {"    info": {        "type": 1,      "address": {          "town": "Bristol",        " County ":" Avon ",        " Country ":" England "      },      " tags ": [" Sport "," Water polo "]   },   " type ":" Basic " }‘

　　

Now, we can extract values and objects from JSON text using the Json_value and Json_query functions:

SELECT  json_value (@json, ' $.type ') as type,  json_value (@json, ' $.info.address.town ') as town,  Json_ QUERY (@json, ' $.info.tags ') as tags

　　

This query would return "Basic", "Bristol", and ["Sport", "water polo"] values. The Json_value function returns one scalar VALUE from JSON text (e.g. strings, numbers, True/false) that's placed on a JS On path specified as the second parameter. Json_query returns an object or array (in this example a array of tags) on the JSON path. JSON built-in functions use javascript-like syntax to reference values and objects in JSON text via second parameter.

The Openjson function enables the reference some array in JSON text and return elements from this array:

Select Valuefrom Openjson (@json, ' $.info.tags ') INSERT into Orders (number, date, Customer, Quantity) SELECT number, date, C Ustomer, Quantity Openjson (@orders) with (number        varchar,         Date datetime,        Customer varchar (200),        Quantity int) As Ordersarray

　　

Four columns in the result set, which is returned by Openjson was defined in the WITH clause. Openjson'll try to find the properties number, Date, Customer, and Quantity with each JSON object and convert their values into columns in the result set. By default, NULL would be returned if the property was not found. The assumption in the query above is, the @orders variable contains the following JSON array:

' [   {' Number ': 1, ' Date ': ' 8/10/2012 ', ' Customer ': ' Adventure works ', ' Quantity ': ' $ '},   {' Number ': 4, ' Date ': ' 5/ 11/2012 "," Customer ":" Adventure Works "," Quantity ": +,   {" Number ": 6," Date ":" 1/3/2012 "," Customer ":" Adventure Works "," Quantity ": +},   {" Number ": 8," Date ":" 12/7/2012 "," Customer ":" Adventure Works "," Quantity ": 2200}] '

　　

As you can see, the transformation from a JSON text to a relational form are simple. You just need to specify column names and types and Openjson would find properties in JSON that match these columns. In this example, plain JSON is used; However, Openjson can handle any nested/hierarchical structure of JSON objects.

Also, Openjson can be used to combine relational and JSON data in the same query. If we assume the JSON array shown in the previous example are stored in the Orders column, the following query can com Bine the columns and JSON fields:

SELECT Id, FirstName, LastName, number, Date, Customer, Quantity from person cross    APPLY Openjson (ordersjson)                            WIT H (number                                        varchar),                                         Date datetime,                                        Customer varchar ($),                                        Quantity int) as Ordersarray

　　

JSON in SQL Server 2016