在SQL Server中串联或合并两个json对象 [英] Concatenate or merge two json objects in SQL Server

问题描述

我有一张表格，其中将json存储在一列中.我想通过合并另一个json来更新json值.

I have a table storing json in one column. I would like to update the json value by merging in another json.

类似的东西:

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

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

这应该导致这样的json:

This should result in json like this:

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

不幸的是，没有这样的JSON_MERGE函数，并且 JSON_MODIFY 只允许我一一修改列.我有太多这样的东西，包括嵌套属性.

Unfortunately there is no such JSON_MERGE function and JSON_MODIFY lets me modify only columns one by one. I have too many of them including nested properties.

我基本上是在寻找与 postgres ||等价的东西.串联运算符.

推荐答案

在Sql Server 2016中，无法在

In Sql Server 2016 it's not possible to use variables as json path in JSON_MODIFY, so I'm not sure if there's an elegant solution for this problem.

如果您拥有Sql Server 2017，那么似乎有可能.

If you have Sql Server 2017, then it seems to be possible.

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添加数组的第一个元素，因此简单的 SELECT 语句，则可以在赋值语句中使用变量的先前值；

sql服务器小提琴演示

PostgreSQL小提琴示例

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

update I've added a bit improved version which should work with different types of values better:

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小提琴演示

这篇关于在SQL Server中串联或合并两个json对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！