Concatenate or merge two json objects in SQL Server

Question

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

Something like:

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

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

This should result in json like this:

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

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.

I'm basically searching for an equivalent to postgres || concatenation operator.

Answer 1

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.

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;

Couple of notes:

• For the sake of simplicity I didn't add checking that both objects are actually valid json;
• I don't know if there's a better way to check that given string is json array or json object;
• It's not possible to add first element of array with json_modify so there's a fallback to simple CONCAT in case first string is an object and second is an array;
• I had to creatively use JSON_QUERY function so jsons will be inserted correctly;
• I've used the fact that if you assign the variable in SELECT statement then you can use previous value of the variable in the assignment statement;

sql server fiddle demo

postgresql fiddle example

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 fiddle demo