Skip to content

Commit 40c0851

Browse files
douglaslMSdouglaslMS
committed
CSat fixes for multiple JSON topics.
1 parent a65b1dd commit 40c0851

9 files changed

Lines changed: 162 additions & 143 deletions

‎docs/relational-databases/json/format-json-output-automatically-with-auto-mode-sql-server.md‎

Lines changed: 25 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
title: "Format JSON Output Automatically with AUTO Mode (SQL Server) | Microsoft Docs"
33
ms.custom:
44
- "SQL2016_New_Updated"
5-
ms.date: "06/02/2016"
5+
ms.date: "07/17/2017"
66
ms.prod: "sql-server-2016"
77
ms.reviewer: ""
88
ms.suite: ""
@@ -23,20 +23,22 @@ manager: "jhubbard"
2323

2424
To format the output of the **FOR JSON** clause automatically based on the structure of the **SELECT** statement, specify the **AUTO** option.
2525

26-
With the **AUTO** option, the format of the JSON output is automatically determined based on the order of columns in the SELECT list and their source tables. You can't change this format.
26+
When you specify the **AUTO** option, the format of the JSON output is automatically determined based on the order of columns in the SELECT list and their source tables. You can't change this format.
2727

28-
The alternative is to use the **PATH** option to maintain control over the output.
29-
- For more info about the **PATH** option, see [Format Nested JSON Output with PATH Mode](../../relational-databases/json/format-nested-json-output-with-path-mode-sql-server.md).
30-
- For an overview of both options, see [Format Query Results as JSON with FOR JSON](../../relational-databases/json/format-query-results-as-json-with-for-json-sql-server.md).
31-
32-
A query that uses the **FOR JSON AUTO** option must have a **FROM** clause.
28+
The alternative is to use the **PATH** option to maintain control over the output.
29+
- For more info about the **PATH** option, see [Format Nested JSON Output with PATH Mode](../../relational-databases/json/format-nested-json-output-with-path-mode-sql-server.md).
30+
- For an overview of both options, see [Format Query Results as JSON with FOR JSON](../../relational-databases/json/format-query-results-as-json-with-for-json-sql-server.md).
31+
32+
A query that uses the **FOR JSON AUTO** option must have a **FROM** clause.
3333

34-
Here are some examples of the **FOR JSON** clause with the **AUTO** option.
34+
Here are some examples of the **FOR JSON** clause with the **AUTO** option.
3535

36-
## Examples
37-
**Query 1**
36+
## Examples
37+
38+
### Example 1
39+
**Query**
3840

39-
The results of the FOR JSON AUTO clause are similar to the results of FOR JSON PATH when only one table is used in the query. In this case, FOR JSON AUTO doesn't create nested objects. The only difference is that FOR JSON AUTO outputs dot-separated aliases (for example, `Info.MiddleName` in the following example) as keys with dots, not as nested objects.
41+
When a query references only one table, the results of the FOR JSON AUTO clause are similar to the results of FOR JSON PATH . In this case, FOR JSON AUTO doesn't create nested objects. The only difference is that FOR JSON AUTO outputs dot-separated aliases (for example, `Info.MiddleName` in the following example) as keys with dots, not as nested objects.
4042

4143
```sql
4244
SELECT TOP 5
@@ -48,7 +50,7 @@ SELECT TOP 5
4850
FOR JSON AUTO
4951
```
5052

51-
**Result 1**
53+
**Result**
5254

5355
```json
5456
[{
@@ -77,10 +79,12 @@ SELECT TOP 5
7779
"Info.MiddleName": "A"
7880
}]
7981
```
82+
83+
### Example 2
84+
85+
**Query**
8086

81-
**Query 2**
82-
83-
When you join tables, columns in the first table are generated as properties of the root object. Columns in the second table are generated as properties of a nested object. The table name or alias of the second table (for example, `D` in the following example) is used as the name of the nested array.
87+
When you join tables, columns in the first table are generated as properties of the root object. Columns in the second table are generated as properties of a nested object. The table name or alias of the second table (for example, `D` in the following example) is used as the name of the nested array.
8488

8589
```sql
8690
SELECT TOP 2 SalesOrderNumber,
@@ -93,7 +97,7 @@ FROM Sales.SalesOrderHeader H
9397
FOR JSON AUTO
9498
```
9599

96-
**Result 2**
100+
**Result**
97101

98102
```json
99103
[{
@@ -113,9 +117,11 @@ FOR JSON AUTO
113117
}]
114118
}]
115119
```
120+
121+
### Example 3
116122

117-
**Query 3**
118-
Instead of using FOR JSON AUTO, you can nest a FOR JSON PATH subquery in the SELECT statement, as shown in the following example. This example outputs the same result as the preceding example.
123+
**Query**
124+
Instead of using FOR JSON AUTO, you can nest a FOR JSON PATH subquery in the SELECT statement, as shown in the following example. This example outputs the same result as the preceding example.
119125

120126
```sql
121127
SELECT TOP 2
@@ -129,7 +135,7 @@ FROM Sales.SalesOrderHeader AS H
129135
FOR JSON PATH
130136
```
131137

132-
**Result 3**
138+
**Result**
133139

134140
```json
135141
[{

‎docs/relational-databases/json/format-nested-json-output-with-path-mode-sql-server.md‎

Lines changed: 7 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
title: "Format Nested JSON Output with PATH Mode (SQL Server) | Microsoft Docs"
33
ms.custom:
44
- "SQL2016_New_Updated"
5-
ms.date: "06/02/2016"
5+
ms.date: "07/17/2017"
66
ms.prod: "sql-server-2016"
77
ms.reviewer: ""
88
ms.suite: ""
@@ -19,9 +19,9 @@ manager: "jhubbard"
1919
# Format Nested JSON Output with PATH Mode (SQL Server)
2020
[!INCLUDE[tsql-appliesto-ss2016-asdb-xxxx-xxx_md](../../includes/tsql-appliesto-ss2016-asdb-xxxx-xxx-md.md)]
2121

22-
To maintain full control over the output of the **FOR JSON** clause, specify the **PATH** option.
22+
To maintain full control over the output of the **FOR JSON** clause, specify the **PATH** option.
2323

24-
**PATH** mode lets you create wrapper objects and nest complex properties. The results are formatted as an array of JSON objects.
24+
**PATH** mode lets you create wrapper objects and nest complex properties. The results are formatted as an array of JSON objects.
2525

2626
The alternative is to use the **AUTO** option to format the output automatically based on the structure of the **SELECT** statement.
2727
- For more info about the **AUTO** option, see [Format JSON Output Automatically with AUTO Mode](../../relational-databases/json/format-json-output-automatically-with-auto-mode-sql-server.md) .
@@ -30,9 +30,9 @@ The alternative is to use the **AUTO** option to format the output automatically
3030
Here are some examples of the **FOR JSON** clause with the **PATH** option. Format nested results by using dot-separated column names or by using nested queries, as shown in the following examples. By default, null values are not included in **FOR JSON** output.
3131

3232
## Example - Dot-separated column names
33-
The following query formats the first five rows from the AdventureWorks Person table as JSON.
33+
The following query formats the first five rows from the AdventureWorks `Person` table as JSON.
3434

35-
The FOR JSON PATH clause uses the column alias or column name to determine the key name in the JSON output. If an alias contains dots, the PATH option creates nested objects.
35+
The **FOR JSON PATH** clause uses the column alias or column name to determine the key name in the JSON output. If an alias contains dots, the PATH option creates nested objects.
3636

3737
**Query**
3838

@@ -52,7 +52,7 @@ SELECT TOP 5
5252
[{
5353
"Id": 1,
5454
"FirstName": "Ken",
55-
"LastName": "Sánchez",
55+
"LastName": "Sanchez",
5656
"Info": {
5757
"MiddleName": "J"
5858
}
@@ -83,7 +83,7 @@ SELECT TOP 5
8383
```
8484

8585
## Example - Multiple tables
86-
If you reference more than one table in a query, FOR JSON PATH nests each column using its alias. The following query creates one JSON object per (OrderHeader, OrderDetails) pair joined in the query.
86+
If you reference more than one table in a query, **FOR JSON PATH** nests each column using its alias. The following query creates one JSON object per (OrderHeader, OrderDetails) pair joined in the query.
8787

8888
**Query**
8989

‎docs/relational-databases/json/validate-query-and-change-json-data-with-built-in-functions-sql-server.md‎

Lines changed: 33 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@
22
title: "Validate, Query, and Change JSON Data with Built-in Functions (SQL Server) | Microsoft Docs"
33
ms.custom:
44
- "SQL2016_New_Updated"
5-
ms.date: "06/02/2016"
5+
ms.date: "07/17/2017"
66
ms.prod: "sql-server-2016"
77
ms.reviewer: ""
88
ms.suite: ""
@@ -22,7 +22,7 @@ manager: "jhubbard"
2222
# Validate, Query, and Change JSON Data with Built-in Functions (SQL Server)
2323
[!INCLUDE[tsql-appliesto-ss2016-asdb-xxxx-xxx_md](../../includes/tsql-appliesto-ss2016-asdb-xxxx-xxx-md.md)]
2424

25-
The built-in support for JSON includes the following built-in functions described in this topic.
25+
The built-in support for JSON includes the following built-in functions described briefly in this topic.
2626

2727
- [ISJSON](#ISJSON) tests whether a string contains valid JSON.
2828

@@ -35,7 +35,7 @@ manager: "jhubbard"
3535
## JSON text for the examples on this page
3636
The examples on this page use the following JSON text, which contains a complex element.
3737

38-
```json
38+
```sql
3939
DECLARE @jsonInfo NVARCHAR(MAX)
4040

4141
SET @jsonInfo=N'{
@@ -55,44 +55,44 @@ SET @jsonInfo=N'{
5555
## <a name="ISJSON"></a> Validate JSON text by using the ISJSON function
5656
The **ISJSON** function tests whether a string contains valid JSON.
5757

58-
The following example returns the JSON text if the column contains valid JSON.
58+
The following example returns rows in which the column `json_col` contains valid JSON.
5959

6060
```sql
61-
SELECT id,json_col
61+
SELECT id, json_col
6262
FROM tab1
63-
WHERE ISJSON(json_col)>0
63+
WHERE ISJSON(json_col) > 0
6464
```
65-
66-
For more info, see [ISJSON &#40;Transact-SQL&#41;](../../t-sql/functions/isjson-transact-sql.md).
65+
66+
For more info, see [ISJSON &#40;Transact-SQL&#41;](../../t-sql/functions/isjson-transact-sql.md).
6767

6868
## <a name="VALUE"></a> Extract a value from JSON text by using the JSON_VALUE function
69-
The **JSON_VALUE** function extracts a scalar value from a JSON string.
69+
The **JSON_VALUE** function extracts a scalar value from a JSON string.
7070

71-
The following example extracts the value of a JSON property into a local variable.
71+
The following example extracts the value of the nested JSON property `town` into a local variable.
7272

7373
```sql
74-
SET @town=JSON_VALUE(@jsonInfo,'$.info.address.town')
74+
SET @town = JSON_VALUE(@jsonInfo, '$.info.address.town')
7575
```
7676

77-
For more info, see [JSON_VALUE &#40;Transact-SQL&#41;](../../t-sql/functions/json-value-transact-sql.md).
77+
For more info, see [JSON_VALUE &#40;Transact-SQL&#41;](../../t-sql/functions/json-value-transact-sql.md).
7878

7979
## <a name="QUERY"></a> Extract an object or an array from JSON text by using the JSON_QUERY function
80-
The **JSON_QUERY** function extracts an object or an array from a JSON string.
80+
The **JSON_QUERY** function extracts an object or an array from a JSON string.
8181

82-
The following example shows how to return a JSON fragment in query results.
82+
The following example shows how to return a JSON fragment in query results.
8383

8484
```sql
85-
SELECT FirstName,LastName,JSON_QUERY(jsonInfo,'$.info.address') AS Address
85+
SELECT FirstName, LastName, JSON_QUERY(jsonInfo,'$.info.address') AS Address
8686
FROM Person.Person
8787
ORDER BY LastName
8888
```
8989

90-
For more info, see [JSON_QUERY &#40;Transact-SQL&#41;](../../t-sql/functions/json-query-transact-sql.md).
90+
For more info, see [JSON_QUERY &#40;Transact-SQL&#41;](../../t-sql/functions/json-query-transact-sql.md).
9191

9292
## <a name="JSONCompare"></a> Compare JSON_VALUE and JSON_QUERY
93-
The key difference between **JSON_VALUE** and **JSON_QUERY** is that **JSON_VALUE** returns a scalar value, while **JSON_QUERY** returns an object or an array.
93+
The key difference between **JSON_VALUE** and **JSON_QUERY** is that **JSON_VALUE** returns a scalar value, while **JSON_QUERY** returns an object or an array.
9494

95-
Consider the following sample JSON text.
95+
Consider the following sample JSON text.
9696

9797
```json
9898
{
@@ -102,9 +102,9 @@ ORDER BY LastName
102102
}
103103
```
104104

105-
In this sample JSON text, data members "a" and "c" are string values, while data member "b" is an array. **JSON_VALUE** and **JSON_QUERY** return the following results:
105+
In this sample JSON text, data members "a" and "c" are string values, while data member "b" is an array. **JSON_VALUE** and **JSON_QUERY** return the following results:
106106

107-
|Query|**JSON_VALUE** returns|**JSON_QUERY** returns|
107+
|Path|**JSON_VALUE** returns|**JSON_QUERY** returns|
108108
|-----------|-----------------------------|-----------------------------|
109109
|**$**|NULL or error|`{ "a": "[1,2]", "b": [1,2], "c":"hi"}`|
110110
|**$.a**|[1,2]|NULL or error|
@@ -113,27 +113,27 @@ ORDER BY LastName
113113
|**$.c**|hi|NULL or error|
114114

115115
## Test JSON_VALUE and JSON_QUERY with the AdventureWorks sample database
116-
Test the built-in functions described in this topic by running the following examples with the AdventureWorks sample database, which contains JSON data. To get the AdventureWorks sample database, [click here](http://www.microsoft.com/en-us/download/details.aspx?id=49502).
116+
Test the built-in functions described in this topic by running the following examples with the AdventureWorks sample database, which contains JSON data. To get the AdventureWorks sample database, [click here](http://www.microsoft.com/en-us/download/details.aspx?id=49502).
117117

118-
In the following examples, the Info column in the SalesOrder_json table contains JSON text.
118+
In the following examples, the `Info` column in the `SalesOrder_json` table contains JSON text.
119119

120120
### Example 1 - Return both standard columns and JSON data
121-
The following query returns both standard relational columns and values from a JSON column.
121+
The following query returns values from both standard relational columns and from a JSON column.
122122

123123
```sql
124-
SELECT SalesOrderNumber,OrderDate,Status,ShipDate,Status,AccountNumber,TotalDue,
124+
SELECT SalesOrderNumber, OrderDate, Status, ShipDate, Status, AccountNumber, TotalDue,
125125
JSON_QUERY(Info,'$.ShippingInfo') ShippingInfo,
126126
JSON_QUERY(Info,'$.BillingInfo') BillingInfo,
127127
JSON_VALUE(Info,'$.SalesPerson.Name') SalesPerson,
128128
JSON_VALUE(Info,'$.ShippingInfo.City') City,
129129
JSON_VALUE(Info,'$.Customer.Name') Customer,
130130
JSON_QUERY(OrderItems,'$') OrderItems
131131
FROM Sales.SalesOrder_json
132-
WHERE ISJSON(Info)>0
132+
WHERE ISJSON(Info) > 0
133133
```
134134

135135
### Example 2- Aggregate and filter JSON values
136-
The following query aggregates subtotals by customer name (stored in JSON) and status (stored in an ordinary column). Then it filters the results by city (stored in JSON), and OrderDate (stored in an ordinary column).
136+
The following query aggregates subtotals by customer name (stored in JSON) and status (stored in an ordinary column). Then it filters the results by city (stored in JSON) and OrderDate (stored in an ordinary column).
137137

138138
```sql
139139
DECLARE @territoryid INT;
@@ -143,22 +143,22 @@ SET @territoryid=3;
143143

144144
SET @city=N'Seattle';
145145

146-
SELECT JSON_VALUE(Info,'$.Customer.Name') AS Customer,Status,SUM(SubTotal) AS Total
146+
SELECT JSON_VALUE(Info, '$.Customer.Name') AS Customer, Status, SUM(SubTotal) AS Total
147147
FROM Sales.SalesOrder_json
148148
WHERE TerritoryID=@territoryid
149-
AND JSON_VALUE(Info,'$.ShippingInfo.City')=@city
150-
AND OrderDate>'1/1/2015'
151-
GROUP BY JSON_VALUE(Info,'$.Customer.Name'),Status
149+
AND JSON_VALUE(Info, '$.ShippingInfo.City') = @city
150+
AND OrderDate > '1/1/2015'
151+
GROUP BY JSON_VALUE(Info, '$.Customer.Name'), Status
152152
HAVING SUM(SubTotal)>1000
153153
```
154154

155155
## <a name="MODIFY"></a> Update property values in JSON text by using the JSON_MODIFY function
156-
The **JSON_MODIFY** function updates the value of a property in a JSON string and returns the updated JSON string.
156+
The **JSON_MODIFY** function updates the value of a property in a JSON string and returns the updated JSON string.
157157

158-
The following example updates the value of a property in a variable that contains JSON.
158+
The following example updates the value of a JSON property in a variable that contains JSON.
159159

160160
```sql
161-
SET @info=JSON_MODIFY(@jsonInfo,"$.info.address[0].town",'London')
161+
SET @info = JSON_MODIFY(@jsonInfo, "$.info.address[0].town", 'London')
162162
```
163163

164164
For more info, see [JSON_MODIFY &#40;Transact-SQL&#41;](../../t-sql/functions/json-modify-transact-sql.md).

‎docs/t-sql/functions/isjson-transact-sql.md‎

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
---
22
title: "ISJSON (Transact-SQL) | Microsoft Docs"
33
ms.custom: ""
4-
ms.date: "06/02/2016"
4+
ms.date: "07/17/2017"
55
ms.prod: "sql-non-specified"
66
ms.reviewer: ""
77
ms.suite: ""
@@ -30,7 +30,7 @@ manager: "jhubbard"
3030

3131
## Syntax
3232

33-
```tsql
33+
```sql
3434
ISJSON ( expression )
3535
```
3636

@@ -49,9 +49,9 @@ ISJSON ( expression )
4949
## Examples
5050

5151
### Example 1
52-
The following example runs a statement block conditionally if the parameter value contains valid JSON.
52+
The following example runs a statement block conditionally if the parameter value `@param` contains valid JSON.
5353

54-
```tsql
54+
```sql
5555
DECLARE @param <data type>
5656
SET @param = <value>
5757

@@ -63,12 +63,12 @@ END
6363
```
6464

6565
### Example 2
66-
The following example returns the JSON text if the column contains valid JSON.
66+
The following example returns rows in which the column `json_col` contains valid JSON.
6767

68-
```tsql
69-
SELECT id,json_col
68+
```sql
69+
SELECT id, json_col
7070
FROM tab1
71-
WHERE ISJSON(json_col)>0
71+
WHERE ISJSON(json_col) > 0
7272
```
7373

7474
## See Also

0 commit comments

Comments
 (0)