dede 网站栏目管理,做服装辅料一般什么网站找客户,免费在线设计平台,网站开发列表本文主要介绍#xff0c;在sqlserver存储过程中#xff0c;使用JSON字符串作为入参#xff0c;并解析使用。 JSON 相关的方法#xff1a;
ISJSON#xff1a; 作用#xff1a;判断一段字符串是否是标准的 jsonFOR JSON PATH#xff1a;作用#xff1a;直接查询数据成 j… 本文主要介绍在sqlserver存储过程中使用JSON字符串作为入参并解析使用。 JSON 相关的方法
ISJSON 作用判断一段字符串是否是标准的 jsonFOR JSON PATH作用直接查询数据成 json 格式 类似于之前的查询一个 xml (FOR XML PATH)JSON_VALUE作用获取json中的节点值获取json中的节点值包含标量值、嵌套对象属性JSON_QUERY作用提取嵌套对象和嵌套数组JSON_MODIFY作用更新一段JSON的内容修改 JSON 对象里的属性值删除 JSON 对象里的某一个属性增加属性OPENJSON解析一段 json 内容
使用示例
以下json字符串示例中包含嵌套对象、数组后面以此json为示例演示。
DECLARE json NVARCHAR(MAX) N{student_id: 10,student_name: 张三,student_age: 18,student_class: {class_id: 2,class_code: snj2,class_name: 三年级2班},student_subjects: [{subject_id: 1,subject_name: 语文},{subject_id: 2,subject_name: 数学}]
};
在JSON中分为标量值可非标量值
标量值 定义单一的、不可再分解的基础数据类型例如上面JSON串顶层字段student_id、student_name、student_age 非标量值 定义可包含多个元素或键值对的复合数据结构例如上面JSON串student_class节点嵌套对象student_subjects嵌套数组
结构类型路径表达式数据类型标量值$.student_id数值标量值$.student_name字符串标量值$.student_age数值嵌套对象$.student_classJSON 对象嵌套数组$.student_subjectsJSON 数组
标量值
解析顶层字段标量值student_id、student_name、student_age --方法1直接JSON_VALUE获取--方法1直接使用JSON_VALUE获取顶层标量字段SELECT JSON_VALUE(json, $.student_id) AS student_id,JSON_VALUE(json, $.student_name) AS student_name,JSON_VALUE(json, $.student_age) AS student_age--方法2使用OPENJSON解析SELECT id as student_id,name as student_name,age as student_ageFROM OPENJSON(json) WITH (id INT $.student_id,name NVARCHAR(50) $.student_name,age INT $.student_age); 嵌套对象
获取非标量值嵌套对象student_class节点中的class_id、class_code、class_name
--方法1直接使用JSON_VALUE通过$.student_class.xx获取SELECT JSON_VALUE(json, $.student_class.class_id) AS class_id,JSON_VALUE(json, $.student_class.class_code) AS class_code,JSON_VALUE(json, $.student_class.class_name) AS class_name--方法2使用OPENJSON解析用$.student_class.xx获取SELECT class_id,class_code,class_nameFROM OPENJSON(json) WITH (class_id NVARCHAR(50) $.student_class.class_id,class_code NVARCHAR(50) $.student_class.class_code,class_name NVARCHAR(50) $.student_class.class_name) AS class--方法3,先JSON_QUERY获取student_class节点然后OPENJSON解析SELECT class_id,class_code,class_nameFROM OPENJSON(JSON_QUERY(json, $.student_class)) WITH (class_id NVARCHAR(50) $.class_id,class_code NVARCHAR(50) $.class_code,class_name NVARCHAR(50) $.class_name) AS class嵌套数组
获取非标量嵌套数组student_subjects中的属性值 --直接使用JSON_VALUE 不可以获取不到SELECT JSON_VALUE(json, $.student_subjects.subject_id) AS class_id,JSON_VALUE(json, $.student_subjects.subject_name) AS class_code--使用OPENJSON解析$.student_subjects.xx读取 不可以获取不到SELECT subject_id,subject_nameFROM OPENJSON(json) WITH (subject_id NVARCHAR(50) $.student_subjects.subject_id,subject_name NVARCHAR(50) $.student_subjects.subject_name) AS subject--正确方法先使用JSON_QUERY获取$.student_subjects节点再OPENJSON解析SELECT subject_id,subject_nameFROM OPENJSON(JSON_QUERY(json, $.student_subjects)) WITH (subject_id INT $.subject_id,subject_name NVARCHAR(50) $.subject_name) AS subject;普通数组
--基本类型数组
DECLARE json NVARCHAR(MAX) N[10,20,30
];
SELECT value AS Number
FROM OPENJSON(json);--引用类型数组
DECLARE json2 NVARCHAR(MAX) N[{id:1,name:张三},{id:2,name:李四}
];
SELECT id,name
FROM OPENJSON(json2)
WITH ( --虚拟表映射id INT $.id, -- 提取每个对象的id字段name NVARCHAR(50) $.name -- 提取每个对象的name字段
); 简单存储过程使用示例
下面还是通过上面的JSON字符串示例演示一下如何存储过程使用JSON字符串接收并在其中解析使用等。
ALTER PROCEDURE ParseStudentJsonjson NVARCHAR(MAX)
AS
BEGIN--json示例
--DECLARE json NVARCHAR(MAX) N{
-- student_id: 10,
-- student_name: 张三,
-- student_age: 18,
-- student_class: {
-- class_id: 2,
-- class_code: snj2,
-- class_name: 三年级2班
-- },
-- student_subjects: [
-- {
-- subject_id: 1,
-- subject_name: 语文
-- },
-- {
-- subject_id: 2,
-- subject_name: 数学
-- }
-- ]
--};-- 验证 JSON 格式IF ISJSON(json) 0BEGINRAISERROR(输入参数不是有效的 JSON 格式, 16, 1);RETURN;ENDcreate table #student(student_id int,student_name varchar(50),student_age int)create table #student_class(class_id int,class_code varchar(50),class_name varchar(50),student_id int)create table #student_subject(subject_id int,subject_name varchar(50),student_id int)-- 解析学生信息 方法1INSERT INTO #student (student_id, student_name, student_age)SELECT JSON_VALUE(json, $.student_id) AS student_id,JSON_VALUE(json, $.student_name) AS student_name,JSON_VALUE(json, $.student_age) AS student_age/**解析顶层字段 方法2INSERT INTO #student (student_id, student_name, student_age)SELECT id as student_id,name as student_name,age as student_ageFROM OPENJSON(json) WITH (id INT $.student_id,name NVARCHAR(50) $.student_name,age INT $.student_age);**/-- 解析班级信息 方法1INSERT INTO #student_class (class_id, class_code, class_name, student_id)SELECT JSON_VALUE(json, $.student_class.class_id) AS class_id,JSON_VALUE(json, $.student_class.class_code) AS class_code,JSON_VALUE(json, $.student_class.class_name) AS class_name,JSON_VALUE(json, $.student_id) AS student_id/**解析班级信息 方法2INSERT INTO #student_class (class_id, class_code, class_name, student_id)SELECT class_id,class_code,class_name,student_idFROM OPENJSON(json) WITH (class_id NVARCHAR(50) $.student_class.class_id,class_code NVARCHAR(50) $.student_class.class_code,class_name NVARCHAR(50) $.student_class.class_name) AS classCROSS APPLY (SELECT student_id FROM #student) AS s; --CROSS APPLY动态关联右侧子查询可引用左侧表的列类似逐行处理有一点点类似join的感觉**//**解析班级信息 方法3INSERT INTO #student_class (class_id, class_code, class_name, student_id)SELECT class_id,class_code,class_name,student_idFROM OPENJSON(JSON_QUERY(json, $.student_class)) WITH (class_id NVARCHAR(50) $.class_id,class_code NVARCHAR(50) $.class_code,class_name NVARCHAR(50) $.class_name) AS classCROSS APPLY (SELECT student_id FROM #student) AS s; **/-- 解析学科信息 INSERT INTO #student_subject (subject_id, subject_name, student_id)SELECT subject_id,subject_name,student_idFROM OPENJSON(JSON_QUERY(json, $.student_subjects)) WITH (subject_id INT $.subject_id,subject_name NVARCHAR(50) $.subject_name) AS subjectCROSS APPLY (SELECT student_id FROM #student) AS s;--这里为了方便只是往临时表写了数据并查出来实际业务可以是根据入参去更新、删除业务表数据select * from #student;select * from #student_class;select * from #student_subject;drop table #student;drop table #student_class;drop table #student_subject;
END总结
JSON_VALUE 提取标量值如上 JSON_VALUE(json, ‘$.student_id’)提取嵌套对象属性如上 JSON_VALUE(json, ‘$.student_class.class_id’) JSON_QUERY提取嵌套对象提取嵌套数组 OPENJSON解析JSON都要用到
