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

Answer 2

Also a bit late to the party, but we faced similar issues trying to merge JSONs in MS SQL. We also wanted it to be recursive and allow us to define strategy for arrays like "union", "concat" and "replace".

Our solution for JSON manipulations like merge, JSON path expressions and more just turned into open source and is now available @ Github

Feel free to use, comment and contribute so we can further improve JSON methods for MS SQL.

Answer 3

You can do something similar to that code:

DECLARE @json1 nvarchar(max),
        @json2 nvarchar(max)

DECLARE @result AS nvarchar(max)

SET @json1 = N'{"a": "1", "c": "3"}'

SET @json2 = N'{"b": "2"}'

SELECT
  @result = COALESCE(@result + ', ', '') + '"' + [key] + '":"' + value + '"'
FROM (SELECT
  [key],
  value
FROM OPENJSON(@json1)
UNION ALL
SELECT
  [key],
  value
FROM OPENJSON(@json2)) AS x

SET @result = '{' + @result + '}'

PRINT @result

the @result is

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