Json1:json operation of data

JSON is a very popular data format for data exchange and is used primarily in web and mobile applications. JSON uses key/value pairs (Key:value pair) to store data and represents nested key-value pairs and arrays of two complex data types, using only commas (referencing key) and brackets (referencing array elements) to route to the specified attribute or member, which is simple and powerful. JSON format is supported in SQL Server 2016 versions, JSON data is represented using Unicode character types, and JSON data can be validated, queried, and modified. A JSON-validated and formatted tool is recommended: JSON formatter.

One, define and validate JSON data

Use nvarchar to represent JSON data and verify that the JSON data is valid through the function Isjson function.

Declare @json nvarchar (Max) Set @json = N ' {"    info": {        "type": 1,      "address": {          "town": "Bristol",        "County": "Avon",        " Country ":" England "      },      " tags ": [" Sport "," water polo "]   },   " type ":" Basic "}' Select Isjson (@json)

The format of the Isjson function is: isjson (expression), which returns 1, indicates that the string is JSON data, returns 0, indicates that the string is not JSON data, and returns NULL, which indicates that expression is null;

Two, the path expression of the JSON data

The path expression is divided into two parts: path mode and path. Path mode is optional (optional) and has two modes: LAX and strict.

1,path Mode

At the beginning of the path expression, path mode can be explicitly declared via the lax or strict keyword, and if not declared, the default path mode is LAX. In LAX mode, if the path expression has an error, the JSON function returns NULL. In strict mode, if the path expression is faulted, the JSON function throws an error;

2,path-expression

Path is the way to access the JSON data, with four types of operators:

• $: Represents the content of the entire JSON data;
• Comma. : Represents a member of a JSON object, also called a field, or key;
• Brackets []: Represents an element in an array where the starting position of the element is 0;
• Key Name: The name of the key that references the corresponding value by key name, and if key name contains spaces, $, commas, brackets, using double quotation marks;

For example, the following JSON data, through the path expression, can be routed to the various properties of the JSON:

{"People":    [      {"name": "John", "surname": "Doe"},      {"name": "Jane", "surname": null, "Active": true}    ]  

The data for the path expression query is:

• $: Represents the contents of the JSON, which is the outermost curly brace of all items, this example is a people array, the subscript of the array is starting from 0;
• $.people[0]: Represents the first element of the people array: {"name": "Jane", "surname": null, "Active": true}
• $.people[0].name: From the first element of the people array, the query key is the data corresponding to the item of name, and this example is John;
• The surname field exists in the middle of the $.people[1].surname:people array because the path expression does not declare path Mode, and the default value is lax, which returns NULL when an error occurs in the path expression;

Third, query JSON data through path

1, query scalar value (json_value)

Use the Json_value (expression, path) function to return a scalar value from the JSON data, based on the path parameter, and the returned data is of the character type.

Declare @json nvarchar(Max)Set @json =N'{"Info": {"type": 1, "address": {"Town": "Bristol", "County": "Avon", "Country": "E Ngland "}," tags ": [" Sport "," Water Polo "]}," type ":" Basic "}'SelectJson_value (@json,'$.type') astype, Json_value (@json,'$.info.type') asInfo_type, Json_value (@json,'$.info.address.town') asTown , Json_value (@json,'$.info.tags[0]') asTag

2, return JSON data (json_query)

Using the json_query (expression [, path]) function, the JSON data (JSON fragment) is returned according to the path parameter, the parameter path is optional (optional), and if you do not specify the option parameter, the default path is $, that is, the entire JSON data is returned.

Declare @json nvarchar(Max)Set @json =N'{"Info": {"type": 1, "address": {"Town": "Bristol", "County": "Avon", "Country": "E Ngland "}," tags ": [" Sport "," Water Polo "]}," type ":" Basic "}'SelectJson_query (@json,'$') asJson_context, Json_query (@json,'$.info') asinfo, json_query (@json,'$.info.address') asinfo_address, Json_query (@json,'$.info.tags') asInfo_tags

Four, modify JSON data through path

Use json_modify (expression, path, newvalue) to modify the value of the property in the JSON data and return the modified JSON data, the process by which the function modifies the JSON data:

• Modify an existing property: Follow the parameter path to find the specified property from the JSON data, modify the property's value to the parameter newvalue, and the return value is the modified JSON data;
• New key-value pair (Key:value pair): If the specified attribute does not exist in the JSON data, a new key value pair is added to the specified path according to the parameter path.
• Delete a key-value pair (Key:value pair): If the value of the parameter newvalue is NULL, then the specified property is removed from the JSON data;
• Append keyword: used to append an element from a JSON array;

example, update,insert,delete and appending data elements to JSON data

Declare @info nvarchar( -)= '{"name": "John", "Skills": ["C #", "SQL"]}'  --Update nameSet @info =Json_modify (@info,'$.name','Mike')  --Insert SurnameSet @info =Json_modify (@info,'$.surname','Smith')  --Delete NameSet @info =Json_modify (@info,'$.name',NULL)  --Add SkillSet @info =Json_modify (@info,'Append $.skills','Azure')

Five. Convert the JSON data to a relational table

The Openjson function is a rowset function (RowSet) that transforms the JSON data into a relational table.

 Openjson (jsonexpression , Path  Span style= "color: #ff0000;"      >]  )  [   [  as JSON   " [   Span style= "color: #ff0000;" >[  as JSON   " ]  [ , ... n  ]  )]  

View Code

• Path parameter: Also called table path, specifies the path of the relational table in the JSON data;
• Column_path parameter: Specifies the path of each column in the relational table JSON based on the path parameter, always explicitly specifying the column path;
• As JSON property: If you specify the as JSON property, then the data type of column must be defined as nvarchar (max), which indicates that the value of the column is JSON data, and if the as JSON property is not specified, the value of the column is a scalar value;
• With option: Specify the schema of the relational table, always specify the WITH option, and if you do not specify the WITH option, the function returns Key,value and type three columns;

example, rendering data in a relational table from JSON data

Declare @json nvarchar(Max)Set @json =N'{"Info": {"type": 1, "address": {"Town": "Bristol", "County": "Avon", "Country": "E Ngland "}," tags ": [" Sport "," Water Polo "]}," type ":" Basic "}'SELECTInfo_type,info_address,tags fromOpenjson (@json,'$.info')  with(Info_typetinyint 'LAX $.type', Info_addressnvarchar(Max)'LAX $.address'  asJson,tagsnvarchar(Max)'LAX $.tags'  asJSON)

Six, storing the relational table data in JSON format

With the for JSON Auto/path, the relational table data is stored in JSON format,

• Auto mode: The format of JSON data is automatically generated according to the order of column in the SELECT statement;
• Path mode: Uses the format of column name to generate the format of the JSON data, and column name uses a comma-delimited (dot-separated) representation of the group-member relationship;

1, generate JSON format in Auto mode

Select ID,    name,    categoryfrom  dbo.dt_json for JSON auto,root ('  json')

The data format returned is

{     "json": [        {           "id": 1,         "name": "C #"         ,"category": "Computer "      },      {           " id ": 2,         " name ":" 中文版 ",         " category ":" Language "       },      {           "id": 3,         "name": "MSDN",         "category": "Web"      },      {           "id": 4,         "name": "Blog",         "category": "Forum"      }   ]}

View Code

2, generate JSON format in path mode

Select ID as ' book.id ',    ' Book.name ',    ' product.category ' fromDbo.dt_jsonfor JSON path,root (' JSON ')

The data format returned is:

{"JSON":[{"Book":{"id": 1,"Name": "C #"},"Product":{"category": "Computer"}},{"Book":{"ID": 2,"Name": "中文版"},"Product":{"category": "Language"}},{"Book":{"ID": 3,"Name": "MSDN"},"Product":{"category": "Web"}},{"Book":{"ID": 4,"Name": "Blog"},"Product":{"category": "Forum"}}]}

View Code

Reference Documentation:

JSON Path Expressions (SQL Server)

JSON Functions (Transact-SQL)

OPENJSON (Transact-SQL)

Format Query Results as JSON with for JSON (SQL Server)

Format Nested JSON Output with PATH Mode (SQL Server)

Format JSON Output automatically with AUTO Mode (SQL Server)

JSON Data (SQL Server)

JSON support in SQL Server 2016

JSON in SQL Server 2016:part 1 of 4

Json1:json operation of data