Using a for JSON clause in SQL Server 2016 to export data as a JSON format

Original address: http://www.ncloud.hk/%E6%8A%80%E6%9C%AF%E5%88%86%E4%BA%AB/export-query-result-as-json-format-in-sql-server-2016/

Use the FOR JSON clause to export the query results as a JSON string, as an attribute that is first available in SQL Server 2016. If you are familiar with the FOR XML clauses, it is easy to understand the for JSON:

Select CColumn, expression, column as Alias from    table1, table2, Table3    forjson [auto | path]

If you add a for JSON clause to the end of a T-SQL SELECT query statement, SQL Server will format the result as a JSON string after returning to the client. Each row of data will be formatted as a JSON object, and each data field will be the value of the row object, and the column name or column alias will be the key to the row object. We have two types of for JSON clauses:

• For JSON Path, which defines the hierarchy of JSON objects through column names or column aliases, the column aliases can contain ".", and the member hierarchy of the JSON will be consistent with the hierarchy in the alias.
  This feature is very similar to the FOR XML PATH clause in earlier versions of SQL Server, and you can use slashes to define the hierarchy of XML.
• For JSON auto, which automatically creates nested JSON sub-arrays in the table structure used in the query statement, similar to the for XML Auto attribute.

If you have used functions and operators in PostgreSQL that involve JSON, you will notice that the FOR JSON clause class is equivalent to the JSON creation function in PostgreSQL such as Row_to_json or Json_object. The primary purpose of the FOR JSON clause is to format variables and columns as JSON objects according to the JSON specification. Like what:

Set @json = (Select1as getdateas@someVar As for JSON path) -- result is: {"Firstkey": 1, "DateKey": "2016-06-15 11:35:21", "Thirdkey": "Content of Variable"}

A for JSON clause is used primarily for scenarios:

• Serialize a set of objects that need to be returned to the client as JSON. Imagine that when you create a JSON Web service, you need to provide vendor information and its product information (such as using the $extend option in an OData service). You may query the vendor list, format each vendor information as a JSON object, and use additional queries to obtain a list of products from this vendor, and convert it to a JSON object array to attach to the vendor object. Other scenarios may use a link query to obtain a list of vendors and product information, which can be formatted as JSON objects using client code (additional queries may be generated if the Entity Framework is used). With the FOR JSON clause, you can connect the two tables to query, add the prefixes you want (define the JSON hierarchy), and complete the JSON formatting at the database level.
• In a one-to-many parent-child table relationship scenario, you do not want to create a child table, but instead want to store the table's records in a JSON array format as a column of the parent table. For example, if you don't want to SalesOrderHeader and salesorderdetails data into two tables, you can format multiple item details for each order as a JSON array to a column in the SalesOrderHeader table.

Using a for JSON clause in SQL Server 2016 to export data as a JSON format