json - 在 SQL Server 中连接或合并两个 json 对象

Question

我有一个表将 json 存储在一列中。我想通过合并到另一个 json 来更新 json 值。

类似于:

insert into mytable 
values ('{ "a": "b" ')

update mytable 
set jsonColumn = JSON_MERGE(jsonColumn, '{ "c": 2 }')

这应该会产生像这样的 json:

{ "a": "b", "c": 2 }

不幸的是，没有这样的 JSON_MERGE 函数和 JSON_MODIFY让我只一一修改列。我有太多的属性，包括嵌套属性。

我基本上是在寻找相当于 postgres || concatenation operator 的内容.

Answer 1

在 Sql Server 2016 中，无法在 JSON_MODIFY 中使用变量作为 json 路径，所以我不确定是否有一个优雅的解决方案来解决这个问题。

如果你有 Sql Server 2017，那么这似乎是可能的。

create function dbo.fn_json_merge
(
    @a nvarchar(max),
    @b nvarchar(max)
)
returns nvarchar(max)
as
begin
    if left(@a, 1) = '{' and left(@b, 1) = '{' begin
        select
            @a = case when d.[type] in (4,5) then json_modify(@a, concat('$.',d.[key]), json_query(d.[value])) else @a end,
            @a = case when d.[type] not in (4,5) then json_modify(@a, concat('$.',d.[key]), d.[value]) else @a end
        from openjson(@b) as d;
    end else if left(@a, 1) = '[' and left(@b, 1) = '{' begin
        select @a = json_modify(@a, 'append $', json_query(@b));
    end else begin
        select @a = concat('[', @a, ',', right(@b, len(@b) - 1));
    end;

    return @a;
end;

一些注释:

• 为了简单起见，我没有添加检查这两个对象是否实际上是有效的 json；
• 我不知道是否有更好的方法来检查给定的字符串是 json 数组还是 json 对象；
• 不可能使用 json_modify 添加数组的第一个元素，因此可以使用简单的 CONCAT 。如果第一个字符串是一个对象，第二个字符串是一个数组；
• 我必须创造性地使用JSON_QUERY函数，以便正确插入 json；
• 我已经使用了这样一个事实:如果您在 SELECT 中分配变量语句，然后您可以在赋值语句中使用变量的先前值；

sql server fiddle demo

postgresql fiddle example

更新我添加了一些改进的版本，它应该可以更好地处理不同类型的值:

create function dbo.fn_json_merge
(
    @a nvarchar(max),
    @b nvarchar(max)
)
returns nvarchar(max)
as
begin
    if left(@a, 1) = '{' and left(@b, 1) = '{' begin
        select @a =
            case
                when d.[type] in (4,5) then
                    json_modify(@a, concat('$.',d.[key]), json_query(d.[value]))
                when d.[type] in (3) then
                    json_modify(@a, concat('$.',d.[key]), cast(d.[value] as bit))
                when d.[type] in (2) and try_cast(d.[value] as int) = 1 then
                    json_modify(@a, concat('$.',d.[key]), cast(d.[value] as int))
                when d.[type] in (0) then
                    json_modify(json_modify(@a, concat('lax $.',d.[key]), 'null'), concat('strict $.',d.[key]), null)
                else
                    json_modify(@a, concat('$.',d.[key]), d.[value])
            end
        from openjson(@b) as d
    end else if left(@a, 1) = '[' and left(@b, 1) = '{' begin
        select @a = json_modify(@a, 'append $', json_query(@b))
    end else begin
        select @a = concat('[', @a, ',', right(@b, len(@b) - 1))
    end

    return @a
end

sql fiddle demo