Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 95 of 212

83 C H A P T E R 5 | Broader data access Figure 5-6: Rows returned by the OPENJSON function with a JSON object array as its argument. By including a WITH clause after the OPENJSON function, you define an explicit schema in which you define the output columns, the data types, and the path of source properties for each column, as shown in Example 5-19. You can add a base path as a second argument to the OPENJSON function. In this example, $."Product Reviews" represents the root path. Because this path name includes an embedded space, it must be enclosed in double quotation marks. Double quotation marks are also required if the name starts with a $ symbol. In the WITH clause, the source property path includes a $ symbol to represent the starting point from the root defined as an argument in the OPENJSON function, followed by the remaining property names in the path when you have nested objects. A dot separates each property name. Although this example returns every property in the JSON data as a column, as shown in Figure 5-7, you can be selective about the columns to return and specify only a subset of properties in the WITH clause. Example 5-19: Using the OPENJSON function to convert a JSON object array to a table DECLARE @json VARCHAR(MAX); SET @json= '{"Product Reviews": [ {"Product":{"ID":709,"Name":"Mountain Bike Socks, M"}, "Review":{"ID":1,"ReviewerName":"John Smith", "Email":"","Rating":5, "Date":"2013-09-18T00:00:00"} }, {"Product":{"ID":937,"Name":"HL Mountain Pedal"}, "Review":{"ID":2,"ReviewerName":"David", "Email":"","Rating":4, "Date":"2013-11-13T00:00:00"} } ] }'; SELECT * FROM OPENJSON(@json, '$."Product Reviews"') WITH ( ProductID int '$.Product.ID', ProductName varchar(100) '$.Product.Name', ReviewID int '$.Review.ID', ReviewerName varchar(100) '$.Review.ReviewerName', ReviewerEmail varchar(100) '$.Review.Email', Rating int '$.Review.Rating', ReviewDate datetime '$.Review.Date' ); Figure 5-7: Rows returned by the OPENJSON function with an explicit schema. Note If the JSON data has two paths with the same name, all built-in JSON functions return the value for the first path. When you store JSON data in a relational table, you use the CROSS APPLY operator with the OPENJSON function to join the table rows with the corresponding JSON data, as shown in Example 5-

Articles in this issue

Archives of this issue

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