Examples of methods for SQL Server to parse/manipulate field data in Json format

This article brings you relevant knowledge aboutSQL server, which mainly introduces that SQL SERVER does not have its own parsing json function, and you need to build a function (table-valued function), as follows This article introduces relevant information about SQL Server parsing/manipulating Json format field data. I hope it will be helpful to everyone.

Recommended study: "SQL Tutorial"

1 json storage

Storing json in sqlserver requires Use string type for storage, generally use nvarchar() or varchar() for storage. Do not use text for storage. When using text, json functions are not supported.

2 json operation

Mainly introduces 5 functions:

(1) openJson: open Json string

(2) IsJson: determine a character Is the string in legal Json format? Returns 1 if yes, 0 if no, and null if null.

(3) Json_Value: Extract value from Json string.

(4) Json_Query: Extract objects or arrays from Json strings.

(5) Json_Modify: Update the attribute value in the Json string and return the updated Json string.

Create a new wm_json_demo table:

CREATE TABLE demo_plus.dbo.wm_json_demo ( id int IDENTITY(1,1) NOT NULL, json_detail varchar(max) NOT NULL, CONSTRAINT PK_wm_json_demo PRIMARY KEY (id) );

The following takes wm_json_demo as an example to demonstrate the above five functions related to json operations

INSERT INTO demo_plus.dbo.wm_json_demo (json_detail) VALUES('{"key":3,"value1":10,"value2":[{"vv21":13,"vv22":103}],"value3":null}'); INSERT INTO demo_plus.dbo.wm_json_demo (json_detail) VALUES('{"key":4,"value1":15,"value2":[{"vv21":13,"vv22":103}],"value3":"10"}'); INSERT INTO demo_plus.dbo.wm_json_demo (json_detail) VALUES('{"key":7,"value1":20,"value2":[{"vv21":13,"vv22":103}],"value3":"15"}');

IsJson: Determine whether a string is legal Json Format. Returns 1 if yes, 0 if no, and null if null.

SELECT IsJson(json_detail) as IsJson from demo_plus.dbo.wm_json_demo

Json_Value: Extract value from Json field

Usage: Json_Value (cloumn_name,'$.json_field_name') from table

Json_Query: Extract objects or arrays from Json strings.

Usage: Json_Query (cloumn_name,'$.json_field_name') from table

SELECT JSON_VALUE(json_detail,'$.key') as 'key', JSON_VALUE(json_detail,'$.value1') as value1, JSON_value(json_detail,'$.value2') as value2, JSON_QUERY(json_detail,'$.value2') as value2_query, JSON_VALUE(json_detail,'$.value3') as value3 from demo_plus.dbo.wm_json_demo;

Json_Modify: Update the Json string Property value and returns updated Json string.

JSON_MODIFY(column_name, '$.json_field', 'change_info');

SELECT JSON_MODIFY(json_detail, '$.value1', 11) as json_detail from demo_plus.dbo.wm_json_demo where JSON_VALUE(json_detail, '$.key')= 7;

Recommended study: "SQL tutorial》

The above is the detailed content of Examples of methods for SQL Server to parse/manipulate field data in Json format. For more information, please follow other related articles on the PHP Chinese website!