declare @info nvarchar(100) = '{"name":"john","skills":["c#","sql"]}' -- update name set @info = json_modify(@info, '$.name', 'mike') -- insert surname set @info = json_modify(@info, '$.surname', 'smith') -- delete name set @info = json_modify(@info, '$.name', null) -- add skill set @info = json_modify(@info, 'append $.skills', 'azure')
五，将JSON数据转换为关系表
OPENJSON函数是一个行集函数（RowSet），能够将JSON数据转换为关系表，
OPENJSON( jsonExpression [ , path ] ) [ WITH ( colName type [ column_path ] [ AS JSON ] [ , colName type [ column_path ] [ AS JSON ] ] [ , . . . n ] ) ]
path 参数：也叫table path，指定关系表在JSON数据中的路径；
column_path 参数：基于path参数，指定每个column在关系表JSON中的路径，应总是显式指定column path；
AS JSON 属性：如果指定AS JSON属性，那么 column的数据类型必须定义为nvarchar(max)，表示该column的值是JSON数据；如果不指定AS JSON属性，那么该Column的值是标量值；
with 选项：指定关系表的Schema，应总是指定with选项；如果不指定with 选项，那么函数返回key，value和type三列；
示例，从JSON数据中，以关系表方式呈现数据
declare @json nvarchar(max) set @json = N'{ "info":{ "type":1, "address":{ "town":"bristol", "county":"avon", "country":"england" }, "tags":["sport", "water polo"] }, "type":"basic" }' SELECT info_type,info_address,tags FROM OPENJSON(@json, '$.info') with ( info_type tinyint 'lax $.type', info_address nvarchar(max) 'lax $.address' as json, tags nvarchar(max) 'lax $.tags' as json )
六，将关系表数据以JSON格式存储
通过For JSON Auto/Path，将关系表数据存储为JSON格式，
Auto 模式：根据select语句中column的顺序，自动生成JSON数据的格式；
Path 模式：使用column name的格式来生成JSON数据的格式，column name使用逗号分隔（dot-separated）表示组-成员关系；
1，以Auto 模式生成JSON格式
select id, name, category from dbo.dt_json for json auto,root('json')
返回的数据格式是
{ "json":[ { "id":1, "name":"C#", "category":"Computer" }, { "id":2, "name":"English", "category":"Language" }, { "id":3, "name":"MSDN", "category":"Web" }, { "id":4, "name":"Blog", "category":"Forum" } ] }
2，以Path模式生成JSON格式
select id as 'book.id', name as 'book.name', category as 'product.category' from dbo.dt_json for json path,root('json')
返回的数据格式是：
{ "json":[ { "book":{ "id":1, "name":"C#" }, "product":{ "category":"Computer" } }, { "book":{ "id":2, "name":"English" }, "product":{ "category":"Language" } }, { "book":{ "id":3, "name":"MSDN" }, "product":{ "category":"Web" } }, { "book":{ "id":4, "name":"Blog" }, "product":{ "category":"Forum" } } ] }