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

Original answer:

A possible approach is using JSON_MODIFY() and JSON_QUERY(), but you need at least SQL Server 2016 to use the built-in JSON support. The idea is to extract the "Cricketer" JSON array from the second JSON (using JSON_QUERY() with the appropriate path) and append it to the first JSON (using JSON_MODIFY()):

JSON:

DECLARE @json1 nvarchar(max) = N'{"Celebrity": [
    {"Name": "SRK", "Surname": "Kajol"},
    {"Name": "Ajay", "Surname": "Devgan"}
]}'
DECLARE @json2 nvarchar(max) = N'{"Cricketer": [
    {"Name": "Virat", "Surname": "Kohli"},
    {"Name": "Sachin", "Surname": "Tendulkar"}
]}'

Statement:

SELECT @json1 = JSON_MODIFY(@json1, '$."Cricketer"', JSON_QUERY(@json2, '$."Cricketer"'))
SELECT @json1

Result:

{"Celebrity": [
    {"Name": "SRK", "Surname": "Kajol"},
    {"Name": "Ajay", "Surname": "Devgan"}
],"Cricketer":[
    {"Name": "Virat", "Surname": "Kohli"},
    {"Name": "Sachin", "Surname": "Tendulkar"}
]}

Update:

If you want to build a JSON output from different tables, the approach below is also an option:

SELECT 
   Celebrity = (SELECT Name, Surname FROM TableA FOR JSON AUTO),
   Cricketers = (SELECT Name, Surname FROM TableB FOR JSON AUTO)
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

SQL Server 2022:

Starting from SQL Server 2022, a combination of JSON_OBJECT() and JSON_QUERY() is also an option:

SELECT JSON_OBJECT(
   'Celebrity': JSON_QUERY(@json1, '$.Celebrity'),
   'Cricketer': JSON_QUERY(@json2, '$.Cricketer')
)

Your problem can be solved by

SELECT  dbo.udf_native_json_merge(@json1,@json2,null)

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

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

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

I don't think that current versions of SQL Server support a MERGE function. The only option is JSON_MODIFY() function, that can either update the value of an existing property, insert a new key:value pair or delete a key.

But in your case, the more appropriate approach is to parse the stored JSON as tables using OPENJSON() with explicit schema, join the tables and rebuild the required JSON output again:

SELECT 
   c = (
      SELECT a.id, a.name, b.age 
      FROM OPENJSON(v.a) WITH (
         id int '$.id',
         name varchar(50) '$.name'
      ) a
      FULL JOIN OPENJSON(v.b) WITH (
         id int '.age'
      ) b ON a.id = b.id
      FOR JSON PATH
   )
FROM (VALUES
   ('[{"id": 1, "name": "Alice"}, {"id":2, "name": "Bob"}]',   '[{"id": 1, "age": 30}, {"id":2, "age": 32}]'),
   ('[{"id": 5, "name": "Charlie"}, {"id":6, "name": "Dale"}]', '[{"id": 5, "age": 20}, {"id":6, "age": 14}]')
) v (a, b)

Result:

c
--------------------------------------------------------------------
[{"id":1,"name":"Alice","age":30},{"id":2,"name":"Bob","age":32}]
[{"id":5,"name":"Charlie","age":20},{"id":6,"name":"Dale","age":14}]

FOR JSON will treat a string as a string even if it represents valid JSON. You need to use JSON_QUERY to convert a JSON string to an actual JSON object. MIN is required to combine multiple rows to one:

SELECT Id, (
  SELECT JSON_QUERY(MIN(CASE WHEN typee = 'APV' THEN jsonStr END)) AS [APV]
       , JSON_QUERY(MIN(CASE WHEN typee = 'VN'  THEN jsonStr END)) AS [VN]
       , JSON_QUERY(MIN(CASE WHEN typee = 'VO'  THEN jsonStr END)) AS [VO]
  FROM t AS x
  WHERE x.id = t.id
  FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
)
FROM t
GROUP BY Id

Demo on db<>fiddle

Couple ideas:

SELECT student,
       [master] --This is a poor choice for an object, as master is the name of a system database
FROM (VALUES((SELECT * FROM dbo.student FOR JSON AUTO),
             (SELECT * FROM dbo.[master] FOR JSON AUTO)))V(student,[master])
FOR JSON AUTO;

SELECT (SELECT *
        FROM dbo.student
        FOR JSON AUTO) AS student,
       (SELECT *
        FROM dbo.[master] --This is a poor choice for an object, as master is the name of a system database
        FOR JSON AUTO) AS [master]
FOR JSON PATH;

While SQL 2016 added support for JSON, I don't think it specifies way to merge or update JSON (only create and query). This means you will need to use DB-specific features, which requires knowing what your database is.

Postgresql has the || operator which will do the job directly:

# select '{"abc":"value1", "xyz":"value2"}'::jsonb || '{"pqr":"value3"}'::jsonb;
                      ?column?                       
-----------------------------------------------------
 {"abc": "value1", "pqr": "value3", "xyz": "value2"}
(1 row)

In other databases, you'll probably have to compose with lower-level utilities (json_set/json_modify) and manual manipulation.

SQLite also has json_patch which would do the job here, however it's a much more powerful and flexible utility based around RFC-7396 JSON Patching, so if the "patcher" is user-controlled it's probably not a good idea.

Firstly, this assumes that your JSON is actually valid. None of your elements (id and name) are quoted, so the JSON is actually invalid. Also I assume your expected results are wrong, as Charlie is give the age of 20, but that age belongs to someone with an id with the value 3, and Charlie's has an id of 5.

Anyway, we can can achieve this with a subquery:

CREATE TABLE dbo.TableA (a nvarchar(MAX));
INSERT INTO dbo.TableA (a)
VALUES(N'[{"id": 1, "name": "Alice"},{"id":2, "name": "Bob"}]'),
      (N'[{"id": 5, "name": "Charlie"},{"id":6, "name": "Dale"}]');
GO
CREATE TABLE dbo.TableB (id int, age int);
INSERT INTO dbo.TableB (id, age)
VALUES (1,30),
       (2,32),
       (3,20),
       (4,14);
GO

SELECT (SELECT OJ.id,
               OJ.[name],
               B.age
        FROM OPENJSON(A.a)
             WITH (id int,
                   [name] nvarchar(50)) OJ
             LEFT JOIN dbo.TableB B ON OJ.id = B.id
        FOR JSON AUTO) a
FROM dbo.TableA A;
     
     
GO
DROP TABLE dbo.TableA;
DROP TABLE dbo.TableB;

db<>fiddle

Yes it is possible for instance by using common table expressions:

WITH cte AS (
   SELECT     @RoleID AS RoleID, Number,Ext
   FROM       OPENJSON(@TelecommunicationsNumber)
   WITH       (RoleID INT, Number NVARCHAR(256), Ext NVARCHAR(256))
)
MERGE Party.TelecommunicationsNumber original
USING cte modified
ON (original.RoleID = modified.RoleID)
WHEN MATCHED
    THEN UPDATE SET
    original.Number = modified.Number,
    original.Ext = modified.Ext
WHEN NOT MATCHED BY TARGET
    THEN INSERT (RoleID,Number,Ext)
    VALUES (@RoleID,modified.Number,modified.Ext);

db<>fidde demo

or:

MERGE Party.TelecommunicationsNumber original
USING (SELECT @RoleID AS RoleID, Number,Ext
       FROM   OPENJSON(@TelecommunicationsNumber)
       WITH   (RoleID INT, Number NVARCHAR(256), Ext NVARCHAR(256))) modified
ON (original.RoleID = modified.RoleID)
WHEN MATCHED
    THEN UPDATE SET
    original.Number = modified.Number,
    original.Ext = modified.Ext
WHEN NOT MATCHED BY TARGET
    THEN INSERT (RoleID,Number,Ext)
    VALUES (@RoleID,modified.Number,modified.Ext);

This may not be the most optimal way to skin the cat but when I need to combine different JSON results in a single output, I typically use the JSON_QUERY() function.

Here is one way to apply it in your case:

SELECT
  COLUMN_NAMES = JSON_QUERY(inf_sch.json_result)
, Notification = JSON_QUERY(notif  .json_result)
FROM
  (
    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'notificacion'
    ORDER BY ORDINAL_POSITION
    FOR JSON PATH
  ) AS inf_sch (json_result)
, (
    SELECT *
    FROM dbo.Notificacion
    FOR JSON PATH
  ) AS notif (json_result)
FOR JSON
  PATH, WITHOUT_ARRAY_WRAPPER

The two derived tables are your two original queries with the ROOT() specifier stripped off. They each produce a JSON output. Each output is essentially injected into the outer level output using the JSON_QUERY function. The function makes sure that its argument is injected as a JSON object rather than an nvarchar string representing one.

The WITHOUT_ARRAY_WRAPPER option at the end of the query makes sure the top-level JSON is returned as a single object rather than an array.

Again, I understand that this may not be the best method. Perhaps a more concise and elegant way could be achieved using the AUTO option of the FOR JSON clause. I admit I am not very familiar with it. Hopefully someone can come along and show both of us, as well as the rest of the audience, how to successfully use it in your scenario.