JSON in SQL Server 2016

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.