Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 92 of 212

80 C H A P T E R 5 | Broader data access "EmailAddress":"","Rating":5, "ReviewDate":"2013-09-18T00:00:00"} ] }, {"ProductID":937,"Name":"HL Mountain Pedal", "pr":[ {"ProductReviewID":2,"ReviewerName":"David", "EmailAddress":"","Rating":4, "ReviewDate":"2013-11-13T00:00:00"} ] } ] */ If you prefer to have control over the formatting and naming of the objects at each level, you can use the FOR JSON PATH clause, as shown in Example 5-14. In this case, you use dot syntax in the column alias to define the names for the nested objects. When you use dot syntax, you provide the object, a dot, and the column alias enclosed in brackets, single quotation marks, or double quotation marks. Optionally, you can include the ROOT option to add a root element to the output. Note You can also use the ROOT option when you use the FOR JSON AUTO clause. Example 5-14: Creating JSON output from two tables with FOR JSON PATH SELECT TOP 2 p.ProductID AS [Product.ID], p.Name AS [Product.Name], pr.ProductReviewID AS [Review.ID], pr.ReviewerName AS [Review.ReviewerName], pr.EmailAddress AS [Review.Email], pr.Rating AS [Review.Rating], pr.ReviewDate AS [Review.Date] FROM Product.ProductReview pr INNER JOIN Production.Product p ON pr.ProductID = p.ProductID FOR JSON PATH, ROOT('Product Reviews'); /* Result {"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"} } ] }*/ Whether using FOR JSON AUTO or FOR JSON PATH, you can also include the following options to modify the formatting of the JSON text: INCLUDE_NULL_VALUES Includes cells with NULL values in the results. By default, these cells are excluded from the output. WITHOUT_ARRAY_WRAPPER Removes the outermost brackets for the JSON output.

Articles in this issue

Archives of this issue

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