How should you complete the Transact-SQL statement?

HOTSPOT

You are a database administrator for an online retail store. You create a table to track orders by running the following Transact-SQL statement:

You are developing a query that will be used as a JSON data source for a web application. The web application requires JSON in the following format:

You need to build the query to return the data for the web application.

How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area. NOTE: Each correct selection is worth one point.

View Answer

Answer:

Explanation:

Box 1: Order.Date In PATH mode, you can use the dot syntax – for example, ‘Item.UnitPrice’ – to format nested output.

Here’s a sample query that uses PATH mode with the FOR JSON clause. The following example also uses the ROOT option to specify a named root element.

Box 2: JSON_VALUE(LineTotal) JSON_VALUE extracts a scalar value from a JSON string.

Box 3: Order.Product

Box 4: FOR JSON PATH When you use the FOR JSON clause, you can specify the structure of the JSON output explicitly, or let the structure of the SELECT statement determine the output.

To maintain full control over the format of the JSON output, use FOR JSON PATH. You can create wrapper objects and nest complex properties.

To format the JSON output automatically based on the structure of the SELECT statement, use FOR JSON AUTO.

Box 5: FOR JSON PATH, ROOT(‘ORDER) Use the ROOT option to specify a named root element.

References: https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json­sql-server

https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql