MSSQL-2022教程

Microsoft SQL Server 2022 JSON教程

1、函数JSON_VALUE() 。 注意：JSON对象的键的名称区分大小写。

declare @v_json_object nvarchar(4000);

begin

set @v_json_object = '{"id":12, "name":"张三"}';

select json_value(@v_json_object, '$.id') as [ID],

JSON_VALUE(@v_json_object,'$.name') as [NAME] ;

end ;

2、JSON_OBJECT(): 直接用键值对构建JSON对象

1. JSON_ARRAY(): 直接用值列表构建JSON数组

4、JSON_PATH_EXISTS(): 检测JSON对象中是否存在指定路径，存在返回1，否则返回0；

-- 检测JSON对象中是否存在指定路径，存在返回1，否则返回0；

declare @v_json_object nvarchar(4000);

begin

set @v_json_object = '{"id":12, "name":"张三"}';

select JSON_PATH_EXISTS(@v_json_object,'$.id') as exists_id ;

end ;

5、JSON_MODIFY(): 修改JSON对象中指定路径的值

-- 修改JSON对象中指定路径的值；

declare @v_json_object nvarchar(4000);

begin

set @v_json_object = '{"id":12, "name":"张三"}';

select JSON_MODIFY(@v_json_object,'$.id',16) as JSON_MODIFY2;

end ;

结果：{"id":16, "name":"张三"}

6、JSON_QUERY()对象中的内嵌的JSON对象或数组；

-- JSON_QUERY()对象中的内嵌的JSON对象或数组；

declare @v_json_object nvarchar(4000);

begin

set @v_json_object = '{"id":12, "name":"张三","attribute":{"age":26,"gender":"男"}}';

select JSON_QUERY(@v_json_object,'$.attribute') as JSON_QUERY2;

end ;

结果： {"age":26,"gender":"男"}

7、openJson(): JSON对象返回表结构。

-- OPENJSON(): JSON对象返回表 , 数组对象，返回多行的表

declare @v_json_object nvarchar(4000);

begin

set @v_json_object = '[{"id":12, "name":"张三","attribute":{"age":26,"gender":"男"}},

{"id":19, "name":"欧阳美","attribute":{"age":28,"gender":"女"}}]';

SELECT * FROM OPENJSON(@V_JSON_OBJECT) with ( [ID] INT '$.id',

[NAME] NVARCHAR(20) '$.name',

[AGE] INT '$.attribute.age',

[GENDR] nvarchar(10) '$.attribute.gender');

END ;

-- OPENJSON(): JSON对象返回表

declare @v_json_object nvarchar(4000);

begin

set @v_json_object = N'{"id":12, "name":"张三","attribute":{"age":26,"gender":"男"}}';

SELECT * FROM OPENJSON(@V_JSON_OBJECT) with ( [ID] INT '$.id',

[NAME] NVARCHAR(20) '$.name',

[AGE] INT '$.attribute.age',

[GENDR] nvarchar(10) '$.attribute.gender');

END ;