Shulou(Shulou.com)05/31 Report--

How to use JSON function in SQL Server, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, there are people who need this can learn, I hope you can gain something.

JSON function

Use the features described in the pages in this section to validate or change JSON text or extract simple or complex values.

For more information about JSON support built into SQL Server, see JSON Data (SQL Server).

2.2 ISJSON

Tests whether the string contains valid JSON.

2.2.1 Examples

2.3 JSON_VALUE

Extract scalar values from JSON strings.

To extract objects or arrays from JSON strings rather than scalar values, see JSON_QUERY (Transact-SQL). For information about the differences between JSON_VALUE and JSON_QUERY, see Comparing JSON_VALUE and JSON_QUERY.

2.4 JSON_QUERY

Extract objects or arrays from JSON strings.

To extract scalar values from JSON strings rather than objects or arrays, see JSON_VALUE (Transact-SQL). For information on the differences between JSON_VALUE and JSON_QUERY, see Comparing JSON_VALUE and JSON_QUERY.

2.5 JSON_MODIFY

Update attribute values in JSON strings and return updated JSON strings.

summary

3 References

【01】https://docs.microsoft.com/zh-cn/sql/t-sql/language-elements/expressions-transact-sql

4 Copyright

Thank you for reading, if there are deficiencies, welcome to advise, learn together, common progress.

Here are some additions

The following is the familiar SELECT and output format, and the following demonstration of JSON is based on this SQL:

For JSON AUTO, Root (''): Add the root node to JOSN

To add a Root Key to FOR JSON, you can customize the ROOT node name with the ROOT option:

For JSON PATH output: The hierarchy of JSON objects can be defined by column aliases.

To customize the structure of output in JSON format, you must use JSONPATH.

FOR JSON Auto automatically creates nested JSON subarrays according to the table structure used in the query statement, similar to the For Xml Auto feature.

FOR JSON Path, which defines the hierarchy of JSON objects by column names or column aliases. Column aliases can contain ". ", JSON's member hierarchy will be consistent with the hierarchy in aliases.

This feature is very similar to the For Xml Path clause in earlier versions of SQL Server, where slashes can be used to define xml hierarchies.

For JSON PATH+ROOT Output: JOSN plus root node

INCLUDE_NULL_VALUES: fields with null values need to be displayed.

NULL data is ignored when outputting JSON. To make NULL fields appear, add the option INCLUDE_NULL_VALUES, which also applies to AUTO.

6. Alias of column, nodes with hierarchical relationship can be added.

For example, the following SQL adds an "SN" node, placing the SERNUM and CLIMAT fields inside:

Parsing data in JSON format

Use OPENJSON() function:

2. Customize the output column through the WITH option:

Third, JSON function

declare @param nvarchar(max);set @param = N'{ "info":{ "type":1, "address":{ "town":"Bristol", "county":"Avon", "country":"England" }, "tags":["Sport", "Water polo"] }, "type":"Basic" }';

ISJSON: Tests whether a string contains valid JSON.

print iif(isjson(@param) > 0, 'OK', 'NO');

Return: OK

JSON_VALUE: Extracts scalar values from JSON strings.

print json_value(@param, '$.info.address.town');

print json_value(@param, '$.info.tags[1]');

Back to Bristol, Water polo

JSON_QUERY: Extract an object or array from a JSON string.

print json_query(@param, '$.info');

{ "type":1, "address":{ "town":"Bristol", "county":"Avon", "country":"England" }, "tags":["Sport", "Water polo"] }

JSON_MODIFY : updates the values of attributes in a JSON string and returns the updated JSON string.

print json_modify(@param, '$.info.address.town', 'London');

Return:

{ "info":{ "type":1, "address":{ "town":"London", "county":"Avon", "country":"England" }, "tags":["Sport", "Water polo"] }, "type":"Basic" }

IV. Precautions

The new built-in JSON in SQL2016 is briefly introduced, mainly as follows:

JSON can be used efficiently in SQL Server 2016, but JSON is not a native data type.

If JSON format is used, an alias must be provided for the output result to be an expression;

The JSON_VALUE and JSON_QUERY functions transfer and fetch data in Varchar format, so you must translate the data to the type you want.

Querying JSON with the help of computed columns can be optimized using indexes.

Did reading the above help you? If you still want to have further understanding of related knowledge or read more related articles, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.