SQL Server JSON native support instance description

Original: SQL Server JSON native support instance description

Background

Microsoft SQL Server is becoming more and more friendly for developers of data platforms. For example, it has been natively supporting XML for many years, and in this trend, it is now possible to use the built-in JSON in SQLServer2016. This is especially valuable for the parsing of some big data interfaces. Compared to what we do now, such as using CLR or custom functions in SQL, the new built-in JSON can greatly improve performance, while optimizing programming and adding and removing changes and other methods.

Does that mean we can discard the XML and start using JSON? Of course not, it depends on the purpose of the data output processing. If you have an external service that interacts with external data through XML and the schemas inside and outside are consistent, then you should use XML data types and native functions. If you are targeting a micro-service architecture or dynamic metadata and data storage, you should take advantage of the latest JSON functions.

Instance

Use the "for JSON" hint behind your T-SQL script to make it easier to format the output when using a data table that queries JSON that already has a fixed schema. Example I used SQL Server worldwide Importers sample database, which can be downloaded directly on GitHub (). Take a look at the view website.customers. we query a data and format the output JSON format:

SELECT [CustomerID]      , [CustomerName]      , [customercategoryname]      , [primarycontact]      , [ Alternatecontact]      , [PhoneNumber]      , [Faxnumber]      , [buyinggroupname]      , [Websiteurl]      , [ Deliverymethod]      , [CityName]       ,deliverylocation.tostring () as Deliverylocation      , [ Deliveryrun]      , [runposition] from  [wideworldimporters].[ Website]. [Customers]  WHERE customerid=1 for  JSON AUTO

　　

Note that we have a geographic data type column (Deliverylocation), which requires two important workarounds (yellow):

First, you need to convert a string character, otherwise you will get an error:

FOR JSON cannot serialize CLR objects. Cast CLR types explicitly into one of the supported types in FOR JSON queries.

Second, JSON uses the syntax of a key-value pair, so you must specify an alias to transform the data, and if it fails, the following error occurs:

Column expressions and data sources without names or aliases cannot be formatted as JSON text using FOR JSON clause. Add alias to the unnamed column or table.

Confirm these, the rewritten formatted output is as follows:

[    {        "CustomerID": 1,        "CustomerName": "Tailspin Toys (Head Office)",        "Customercategoryname": "Novelty Shop ",        " primarycontact ":" Waldemar Fisar ",        " alternatecontact ":" Laimonis berzins ",        " PhoneNumber ":" (308 ) 555-0100 ",        " Faxnumber ":" (308) 555-0101 ",        " Buyinggroupname ":" Tailspin Toys ",        " Websiteurl "," http:// Www.tailspintoys.com ",        " Deliverymethod ":" Delivery Van ",        " CityName ":" Lisco ",        " deliverylocation ":" Point ( -102.6201979 41.4972022) ",        " Deliveryrun ":" ","        runposition ":" "    }]

　　

Of course, you can also use JSON as an input DML statement, such as using "Openjson" in a insert/update/delete statement. JSON hints can therefore be added to all data operations.

If you do not understand the data structure or want to make it more flexible, you can store it as a character type in JSON format, and the type of the column can make the nvarchar type. The customfields column in the application.people table is typically the case. You can use the following statement to look at the contents of this column in tabular format:

declare @json nvarchar (max) SELECT @json =[customfields]from [wideworldimporters]. [Application]. [People]where Personid=8select * from Openjson (@json)

　　

The result set is displayed in the table results:

Another way to query this record, if you need to know the JSON data structure and key names, use the Json_value and Json_query functions:

  SELECT       json_query ([CustomFields], ' $. Otherlanguages ') as Otherlanguages,       json_value ([CustomFields], ' $. HireDate ') as HireDate,       json_value ([CustomFields], ' $. Title ') as title,       json_value ([CustomFields], ' $. Primarysalesterritory ') as Primarysalesterritory,       json_value ([CustomFields], ' $. Commissionrate ') as commissionrate from  [wideworldimporters].[ Application]. [People]  where personid=8

　　

Show the results of table formatting in a tabular result set:

This place is most concerned with querying conditions and adding indexes. Imagine that we're going to look at all the people hired since 2011, and you can run the following query statement:

SELECT personid,fullname,json_value (CustomFields, ' $. HireDate ') as Hiredatefrom [Wideworldimporters]. [Application]. [People]where Isemployee=1and year (CAST (Json_value (CustomFields, ' $. HireDate ') as date) >2011

　　

Remember that Json_value returns a single literal value (nvarchar (4000)). You need to convert the return value into a Time field, and then isolate the year to filter the query criteria. The actual implementation plan is as follows:

To verify how the JSON content is indexed, you need to create a computed column. For illustrative purposes, the Application.people table marks the version and joins the computed column, which is not supported when the system version is on. We use the Sales.invoices table here, where JSON data is inserted in returneddeliverydata . Then get the data and feel it:

SELECT TOP [Invoiceid]      , [CustomerID]      , Json_query ([Returneddeliverydata], ' $. Events ') from  [wideworldimporters].[ Sales]. [Invoices]

　　

Discover result set the first event is "Ready for collection":

Then get the March 2016 invoice data:

SELECT [Invoiceid]      , [CustomerID]      , CONVERT (datetime, CONVERT (Varchar,json_value ([Returneddeliverydata], ' $ . Events[0]. Eventtime '), 126) from  [wideworldimporters].[ Sales]. [Invoices]  WHERE CONVERT (datetime, CONVERT (Varchar,json_value ([Returneddeliverydata], ' $. Events[0]. Eventtime '), 126)       between ' 20160301 ' and ' 20160331 '

　　

The actual implementation plan is as follows:

Add a computed column called "Readydate" and prepare the result of the set expression:

ALTER TABLE [Wideworldimporters]. [Sales]. [Invoices] ADD readydate as CONVERT (datetime, CONVERT (Varchar,json_value ([Returneddeliverydata], ' $. Events[0]. Eventtime ')), 126)

　　

After that, the query is re-executed, but the new computed column is used as the condition:

SELECT [Invoiceid]      , [CustomerID]      , readydate from  [wideworldimporters].[ Sales]. [Invoices]  WHERE readydate between ' 20160301 ' and ' 20160331 '

　　

The execution plan is the same, except for the missing index recommended by SSMs:

Therefore, an index on the computed column is recommended to help with the query, and the index is indexed as follows:

/*the Query Processor estimates that implementing the following index could improve the Query cost by 99.272%.*/create NON CLUSTERED INDEX Ix_invoices_readydateon [Sales]. [Invoices] ([Readydate]) INCLUDE ([Invoiceid],[customerid]) GO

　　

We re-execute the query validation execution plan:

With the index, the performance is greatly improved, and the speed of the query JSON is as fast as the table column.

Summarize:

This article provides a brief introduction to the new built-in JSON in SQL2016, with the following main points:

• JSON can be used efficiently in SQLServer2016, but JSON is not a native data type;
• If you use the JSON format you must provide an alias for the output to be an expression;
• The Json_value and Json_query functions transfer and retrieve data in varchar format, so the data must be translated into the type you want.
• Querying JSON with the help of computed columns can be optimized using an index.

SQL Server JSON native support instance description