AdventureWorksCTP3 JSON Sample
New database and samples for AdventureWorks 2016 CTP3 database are published on AdventureWorks Sample Databases and Scripts for SQL Server 2016 CTP3. In SQLServer2016CTP3Samples.zip file (attached in this post) you can find various example scripts that show how to use various features added in SQL Server 2016.
One of the folders in this archive is JSON folder where you can find some JSON example scripts. In this post we will see short description of the files in this folder.
New JSON tables
When you download AdventureWorks2016CTP3 database you will notice that there are a lot of new tables with sufixes _json, _temporal_inmemory, etc. These are new tables that are used in example scripts.
For JSON support are added two new tables:
- Person.Person_json table that will contain information related to person records
- Sales.SalesOrder_json table that will contain information related to Sales orders
These tables are just copies of the existing Person.Person and Sales.SalesOrderHeader tables. Note that when you install AdventureWorks2016CTP3 database you don't have any JSON data in this table - you will need to run de-normalization script described below to populate tables with JSON data.
JSON script examples
In SQLServer2016CTP3Samples.zip (attached) you will find JSON folder with the following content:
These are scripts that show how to use various JSON features in this database. In the following sections we will see short explanations of these scripts.
De-normalization
First script you would need to run is de-normalization script. Even if you don't need de-normalization you will need this script because this is prerequisite for all other scripts.
This script generates JSON data and stores JSON text in new Person.Person_json and Sales.SalesOrder_json tables. In this script you will see SQL statements that perform following actions:
- Create two new JSON columns in Person.Person_json table that will contain JSON text with arrays of phone numbers and email addresses
- Populate PhoneNumbers JSON column with the content of related phone numbers from Person.PersonPhone and Person.PhoneNumberType tables.
- Populate EmailAddresses JSON column with the content of related email addresses from Person.EmailAddress table.
- Create three new JSON columns in Sales.Sales_json table that will contain sales reasons and order items, and various information about sales orders
- Populate SalesReasons JSON column with the content of related sales reasons from Sales.SalesOrderHeaderSalesReason and Sales.SalesReason tables.
- Populate OrderItems JSON column with the content of related order items from Sales.SalesOrderDetail tables.
- Populate Info JSON column with the billing/shipping information, salesman, customer information, etc.
This script shows how you can transform complex relational structure into de-normalized JSON collections.
Also, this script is important because it initializes new JSON tables with the content that is required for all procedures, views, and queries in the sample.
Run this script even if you don't need de-normalization because without this script you will not have JSON column and JSON content in Person.Person_json and Sales.SalesOrder_json tables.
JSON views and procedures
These two files show how you can create stored procedures and views that query and process JSON text in Person.Person_json and Sales.SalesOrder_json tables.
First JSON view Sales.vwSalesOrderItems_json shows how you can create relational "table view" on the array of order items stored in OrderItems in Sales.SalesOrder_json table.
Second JSON view Sales.vwSalesOrderInfo_json returns information about shipping address and method, salesman, and customer from Info JSON column. This view shows how you can encapsulate values in JSON text end expose them as regular columns. If you don't want to use JSON data structures, you also have Sales.vwSalesOrderInfoRel_json view that shows how this query would look in the original relational schema.
JSON procedures shows different use case that you might use for JSON processing such as:
- Querying new tables with JSON columns
- Selecting information from tables using the list of ids formatted as JSON array
JSON indexes
In this file you can see how to create B-tree or full-text search indexes on JSON columns, This file also contains some stored procedures with queries that use new indexes.
Show actual execution plan option in SQL Server Management Studio when you runt these stored procedures to verify that queries use indexes during execution.
JSON import/export
In this file you find procedures that export table data from Person.Person_json and Sales.SalesOrder_json tables and import JSON text into these tables. This script demonstrates how easily you can transform relational data to JSON and vice versa.
JSON query examples
In this file you find various queries that use views and stored procedures created in previous scripts.
Cleanup script
In this file you find script that deletes all new columns, JSON data, stored procedures, indexes, and views that are created in previous steps.