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

In this issue, the editor will bring you about the role of the JSON function in SQL Server. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

2.1 JSON function

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

For more information about built-in JSON support in SQL Server, see JSON data (SQL Server).

2.2 ISJSON

Test whether the string contains a valid JSON.

2.2.1 exampl

2.3 JSON_VALUE

Extract scalar values from the JSON string.

To extract an object or array from a JSON string instead of a scalar value, 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 an object or array from a JSON string.

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

2.5 JSON_MODIFY

Updates the property value in the JSON string and returns the updated JSON string.

Summary

3 references

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

4 copyright

Thank you for your reading. If there are any inadequacies, you are welcome to comment, study together and make progress together.

Here are some additions

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

2. FOR JSON AUTO,Root (''): add root node to JOSN. To add Root Key to FOR JSON, you can use the ROOT option to customize the name of the ROOT node:

3. FOR JSONPATH output: you can define the hierarchy of JSON objects through column aliases. If you want to customize the structure of the output JSON format, you must use JSONPATH.

FOR JSON Auto, which 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, defines the hierarchy of JSON objects by column names or column aliases. Column aliases can contain "." the member hierarchy of JSON 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 the hierarchy of xml.

4. FOR JSON PATH+ROOT output: add the root node to the JOSN

5. INCLUDE_NULL_VALUES: the field with the value of null needs to be displayed. Data for NULL will be ignored when exporting JSON. If you want the NULL field to be displayed, you can add the option INCLUDE_NULL_VALUES, which is also applicable to AUTO.

6. the alias of the column, which can add nodes with hierarchical relationships. For example, the following SQL adds a "SN" node with fields SERNUM and CLIMAT in it:

Second, parsing the data in JSON format

1. Use the OPENJSON () function:

2. Use the WITH option to customize the output column:

3. 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"}'

1. ISJSON: test whether the string contains a valid JSON.

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

Return: OK

2. JSON_VALUE: extract scalar values from JSON strings.

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

Return: Bristol,Water polo

3. 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"]}

4. JSON_MODIFY: updates the value of the attribute in the 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. matters needing attention

The new built-in JSON in SQL2016 is briefly introduced. The main points are as follows:

JSON can be used efficiently in SQLServer2016, but JSON is not a native data type; if you use JSON format, you must provide an alias for the output that is an expression; JSON_VALUE and JSON_QUERY functions transfer and obtain data in Varchar format, so you must translate the data into the type you need. With the help of computed columns, the query JSON can be optimized using indexes.

The above is the role of the JSON function in SQL Server shared by Xiaobian. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.