sql-server - T-SQL Json_modify 将属性附加到每个对象

Question

我有一个存储过程，它接受一个 JSON 字符串作为输入参数。输入的 JSON 字符串是这样的:

[
    {
        "name":"Jhon",
        "surname":"Smith",
        "skills":["C#","VB.NET"]
    },
    {
        "name":"Robert",
        "surname":"Jhonson",
        "skills":["T-SQL","Pascal"]
    }
]

如何自动为每个主体对象添加唯一的 GUID 属性？

Answer 1

查看您已经发现的示例数据this page介绍如何使用 json_modify() 函数插入值的文档。该页面上的示例是为单个“主要对象”编写的。

如果我对此的解释正确，那么您的示例有 2 个主要对象。使用another page文档的一部分显示了如何使用 openjson() 函数将该示例分成几行。然后，您可以在每一行的第一个文档页面中应用 json_modify()。

declare @var nvarchar(max) =
'[
   {
     "name":"Jhon",
     "surname":"Smith",
     "skills":["C#","VB.NET"]
   },
   {
     "name":"Robert",
     "surname":"Jhonson",
     "skills":["T-SQL","Pascal"]
   }
]';

select row_number() over(order by (select null)) as ObjNumber,
       json_modify(j.value, '$.guid', convert(nvarchar(100), newid())) as ObjValue
from openjson(@var, '$') j

结果是这样的:

ObjNumber   ObjValue
----------- ----------------------------------------------------
1           {
                "name":"Jhon",
                "surname":"Smith",
                "skills":["C#","VB.NET"]
                ,"guid":"154C5581-588C-41AA-B292-BB6459F8F4DC"}
2           {
                "name":"Robert",
                "surname":"Jhonson",
                "skills":["T-SQL","Pascal"]
                ,"guid":"46ACFDD6-58DE-4DB0-8D7A-9B1CCABFF8D8"}

Fiddle

要将行重新组合在一起，只需添加 for json path。然而，这确实需要在输出中结束的字段别名(此处为 MyObjects)。

select json_modify(j.value, '$.guid', convert(nvarchar(100), newid())) as MyObjects
from openjson(@var, '$') j
for json path;

输出:

[{"MyObjects":{
     "name":"Jhon",
     "surname":"Smith",
     "skills":["C#","VB.NET"]
   ,"guid":"FCED4D30-B2B0-460B-97FA-EDA820039572"}},{"MyObjects":{
     "name":"Robert",
     "surname":"Jhonson",
     "skills":["T-SQL","Pascal"]
   ,"guid":"9FF02A70-0455-4E5C-8C11-27BB2688929D"}}]

Fiddle

要更新变量，请使用以下代码。奖励:replace() 删除了之前添加的字段别名。

set @var = replace(
             ( select json_modify(j.value, '$.guid', convert(nvarchar(100), newid())) as MyObjects
               from openjson(@var, '$') j
               for json path ),
             '"MyObjects":', '');

select @var 的最终输出:

[{{
     "name":"Jhon",
     "surname":"Smith",
     "skills":["C#","VB.NET"]
   ,"guid":"66CB37D3-FAEF-4186-94D8-8AC0CF6EB1AC"}},{{
     "name":"Robert",
     "surname":"Jhonson",
     "skills":["T-SQL","Pascal"]
   ,"guid":"564D6904-D981-40AC-BA9C-8B06015ACE50"}}]

Fiddle