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

This article brings you relevant knowledge about SQL 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!