Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 93 of 212

81 C H A P T E R 5 | Broader data access Importing JSON data In some cases, you might choose to use a hybrid approach by storing JSON data in a relational table. This approach is useful when the data that you need to store has a simple structure and does not change frequently or has properties that are needed only for a few rows in a table. With this approach, you can choose to store some data in standard relational structures and other data as JSON text and thereby combine the structures to best meet your performance and application requirements. One option is to import data from an existing relational source and convert it to JSON before loading it into a table, as shown in Example 5-15. The target column for the JSON data must have a varchar or nvarchar data type unless you prefer to compress the data first. In that case, you use a column with a varbinary data type instead. Optionally, the column can have a constraint using the ISJSON function that we describe later in this chapter. Example 5-15: Importing data as JSON text into a table -- Create a varchar/nvarchar column in the target table ALTER TABLE Production.Product ADD Reviews NVARCHAR(MAX) CONSTRAINT [JSON format for Reviews] CHECK(ISJSON(Reviews)>0); -- Update JSON column with data from another SQL table UPDATE Production.Product SET Reviews = (SELECT ReviewerName as [Reviewer.Name], EmailAddress as [Reviewer.Email], Rating, ReviewDate FROM Production.ProductReview WHERE Production.ProductReview.ProductID = Production.Product.ProductID FOR JSON PATH, WITHOUT_ARRAY_WRAPPER); An alternative is to insert JSON data as a constant value into a relational table, as shown in Example 5- 16. In this case, you format the string as the value for a single column with a varchar or nvarchar data type. Example 5-16: Importing JSON data as a constant into a table CREATE TABLE Production.ProductAlternate ( Name NVARCHAR(50) NOT NULL, Reviews NVARCHAR(MAX) NULL ) INSERT INTO Production.ProductAlternate(Name, Reviews) VALUES('Cycling Socks, M', '[ {"Reviewer":{"Name":"John Smith","E-mail":""}, "Rating":3,"ReviewDate":"2016-01-20T00:00:00"}, {"Reviewer":{"Name":"Jill","E-mail":""}, "Rating":4,"ReviewDate":"2016-02-10T00:00:00" ]') Converting JSON data to a table structure When you need to convert JSON to a relational format, you can use the new OPENJSON function. With this technique, you do not need to preprocess JSON data in the application layer before you import it into a table or use it in a T-SQL query.

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016