CODEWITHSUNDEEP
jsonsql-serversql-server-json
Jan Blaha
Jan Blaha

Reputation: 3095

Concatenate or merge two json objects in SQL Server

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.

Upvotes: 11

Views: 35634

Answers (9)

Aleksandr Krasikov
Aleksandr Krasikov

Reputation: 59

Just so as not to lose.

The final version of a function from "Roman Pekar" with all improvements:

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) IS NOT NULL then json_modify(@a, concat('$."', d.[key],'"'), cast(d.[value] as int))
        when d.[type] in (2) and try_cast(d.[value] as float) IS NOT NULL then json_modify(@a, concat('$."', d.[key],'"'), cast(d.[value] as float))
        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;

Upvotes: 0

Karcan
Karcan

Reputation: 213

I have a solution for your issue. I found it while trying to merge 2 different JSON objects and I used JSON_MODIFY and OPENJSON functions.

Sample data:

JSON1: {"a": 1, "b": 2, "c": 3}
JSON2: {"d": 4, "e": 5}

Here the solution

DECLARE @vJSON NVARCHAR(MAX) = N'{"a":1, "b":2, "c":3}'
DECLARE @vJSON2 NVARCHAR(MAX) = N'{"d":4, "e":5}'

SELECT
    @vJSON = JSON_MODIFY(@vJSON, CONCAT(N'$.', [Key]), value)
FROM
OPENJSON(@vJSON2)

SELECT @vJSON
--Output: {"a":1, "b":2, "c":3, "d":"4", "e":"5"}

Also it's not a type-safe solution, you can add a case statement to casting values according to type of OPENJSON

Upvotes: 4

ankush
ankush

Reputation: 999

Based on @JorgeRibeiro answer , if you want to edit existing value also this will work only for one level json-

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

DECLARE @result AS nvarchar(max)

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

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

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

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

PRINT @result

Upvotes: 0

user62251
user62251

Reputation: 1

Could also possibly look at:

SELECT (
    SELECT 
    (
        SELECT    ID AS "test.id" 
        FROM      [Table1] 
        FOR       JSON AUTO
    )             AS 'test1', 
                  'id' AS 'test2'
    FROM          test2
    FOR           JSON AUTO
)                 AS JSON

Upvotes: 0

Anyvado
Anyvado

Reputation: 71

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.

Upvotes: 3

roman
roman

Reputation: 117370

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

Upvotes: 7

Impulse
Impulse

Reputation: 11

I'm a bit late to the party, but I'm running into something similar at the moment. I made a solution based off of this problem that will merge top-level JSON items.

Some examples of what this would do:

{"a":1} + {"B":2} = {"a":1,"B":2}
{"x":true,"y":{"a":"b","c":"d"}} + {"y":{"a":"z"}} = {"x":true,"y":{"a":"z"}}

This version would not drill down to merge sub-items (for example, it would not keep the ["y"]["c"] index in my second example). I'd imagine that it could be enhanced to do so, but this was a quick proof-of-concept version and I don't need to worry about those kind of updates for my purposes.

Content:

--- Merge the top-level items of two JSON object strings into one JSON
--- based off of: https://stackoverflow.com/questions/47489030/generate-a-json-string-containing-the-differences-in-two-other-json-strings-usin

 DECLARE  @jsonA     NVARCHAR(MAX) = '{"CommonValue":"OriginalThing", "OldValue": "A", "ComplexValue": {"InnerValue": "ABC"}}'
        ,@jsonB     NVARCHAR(MAX) = '{"CommonValue":"ChangedThing", "NewValue": "B", "Number": 22}'
        ,@result    NVARCHAR(MAX) = ''

--- Catalog of differences.
DECLARE @JsonDiff TABLE 
(
    OldKey CHAR(128),
    OldValue NVARCHAR(MAX),
    OldType CHAR(1),
    NewKey CHAR(128),
    NewValue NVARCHAR(MAX),
    NewType CHAR(1)
)
--- Temporary table for output rows.
--- The table could probably clipped out for production stuff.
--- For proof-of-concept, it's useful for querying results
---  before building the JSON string.
DECLARE @JsonData TABLE 
(
    NewKey CHAR(128),
    NewValue NVARCHAR(MAX),
    NewType CHAR(1)
)

;WITH DSA AS
(
    SELECT *
    FROM OPENJSON(@jsonA)   
)
,DSB AS
(
    SELECT *
    FROM OPENJSON(@jsonB)   
)
INSERT INTO @JsonDiff (OldKey, OldValue, OldType, NewKey, NewValue, NewType)
SELECT a.[Key] aKey, a.[Value] aValue, a.[Type] aType, b.[Key] bKey, b.[Value] bValue, b.[Type] bType
FROM DSA A
FULL OUTER JOIN DSB B ON A.[key] = B.[key]

INSERT INTO @JsonData (NewKey, NewValue, NewType) 
    SELECT OldKey as k, OldValue as v, OldType as t
    FROM @JsonDiff
    WHERE OldKey IS NOT NULL AND NewKey IS NULL
    UNION
    SELECT NewKey as k, NewValue as v, NewType as t
    FROM @JsonDiff
    WHERE NewKey IS NOT NULL

--- a few queries for display purposes
--- select * FROM @JsonDiff
select NewKey, NewValue FROM @JsonData

SELECT @result += CONCAT ( '"', TRIM([NewKey]), '":'
    ,IIF([NewType] = 1, CONCAT('"', [NewValue], '"'), [NewValue]) -- If the item is a string, then add quotes.
    ,','
)
FROM @JsonData

--- Print the JSON
SELECT CONCAT('{', LEFT(@result, LEN(@result) - 1), '}')

Edit: Here's a slightly more streamlined version of the last bit that removes the need to have @JsonData:

SELECT @result += CONCAT ( '"', TRIM([k]), '":'
    ,IIF([t] = 1, CONCAT('"', [v], '"'), [v]) -- If the item is a string, then add quotes.
    ,','
)
FROM 
    (
        SELECT OldKey as k, OldValue as v, OldType as t
            FROM @JsonDiff
            WHERE OldKey IS NOT NULL AND NewKey IS NULL
        UNION
        SELECT NewKey as k, NewValue as v, NewType as t
            FROM @JsonDiff
            WHERE NewKey IS NOT NULL
    ) as mid

--- Print the JSON
SELECT CONCAT('{', LEFT(@result, LEN(@result) - 1), '}')

Upvotes: 0

Pawan Kumar
Pawan Kumar

Reputation: 2011

There is concept of append in JSON_MODIFY but that is dependent on the tag name. Please see the example below. if you have tag name then it can work otherwise not. Read more from https://learn.microsoft.com/en-us/sql/t-sql/functions/json-modify-transact-sql. Also note that at one point in time you can append a single value

PRINT 'EXAMPLE 1

'

DECLARE @j NVARCHAR(MAX)
SET @j = '{"k" : ["a","b"] }'
PRINT @J
SET @J=JSON_MODIFY(JSON_MODIFY(@j,'append $.k','c'),'append $.k','2')
PRINT @J
GO

PRINT '
EXAMPLE 2

'

DECLARE @j NVARCHAR(MAX)
SET @j = '{"a":"b"}'
PRINT @J
SET @J=JSON_MODIFY(@J,'append $','c:2')
PRINT @J
GO

OUTPUT

EXAMPLE 1

{"k" : ["a","b"] }
{"k" : ["a","b","c","2"] }

EXAMPLE 2

{"a":"b"}
{"a":"b"}

Upvotes: 0

Jorge Ribeiro
Jorge Ribeiro

Reputation: 1138

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"}

Upvotes: 2

Related Questions