SQL Server存储过程JSON_MERGE报错，求替代方案实现JSON字段追加

我需要编写一个存储过程，实现以下逻辑：

• 接收JsonInput参数，提取其中的Year、Surname、Land信息
• 查询数据库表dbo.myTable获取对应值@value
• 将@value作为MyValue字段添加到最终的JsonOutput中，输出格式保留原输入的所有字段，新增MyValue

现有代码执行时遇到错误：'JSON_MERGE' 不是可识别的内置函数名称——因为SQL Server并未提供JSON_MERGE函数（该函数属于MySQL）。

现有代码 #

declare @JsonInput nvarchar(MAX),
@JsonOutput nvarchar(MAX) 

set @JsonInput= N'{"Year":2022,"Surname":"Axel","Land":"Asia"}'

select @JsonInput

--define local variables
declare @name nvarchar(50), 
        @year int,
        @land nvarchar(50),
        @value nvarchar(50)
        

-- get json input data for dataset
set @name = JSON_VALUE(@JsonInput,'$.Surname')
set @year = JSON_VALUE(@JsonInput,'$.Year')
set @land = JSON_VALUE(@JsonInput,'$.Land')

--get  value
select @value = [Value] from  dbo.myTable  where ID='6' and [Year]=@year and Land=@land
select @value 

set @JsonOutput = (
select *
from dbo.myTable  where Surname=@name and [Year]=@year and Land=@land
for json auto)

Set @JsonOutput = JSON_MERGE(jsonColumn, '{"MyValue":"@value"}')
select @JsonOutput

错误信息 #

'JSON_MERGE' 不是可识别的内置函数名称。

期望输出 #

{"Year":2022,"Surname":"Axel","Land":"Asia","MyValue":"3"}

解决方案 #

方法一：用JSON_MODIFY直接扩展原JSON #

SQL Server自带的JSON_MODIFY函数可以直接修改JSON结构，添加新属性，刚好满足需求：

declare @JsonInput nvarchar(MAX),
@JsonOutput nvarchar(MAX) 

set @JsonInput= N'{"Year":2022,"Surname":"Axel","Land":"Asia"}'

--define local variables
declare @name nvarchar(50), 
        @year int,
        @land nvarchar(50),
        @value nvarchar(50)
        

-- get json input data for dataset
set @name = JSON_VALUE(@JsonInput,'$.Surname')
set @year = JSON_VALUE(@JsonInput,'$.Year')
set @land = JSON_VALUE(@JsonInput,'$.Land')

--get  value
select @value = [Value] from  dbo.myTable  where ID='6' and [Year]=@year and Land=@land

-- 直接在原JSON基础上添加MyValue字段，用ISNULL处理空值避免字段缺失
SET @JsonOutput = JSON_MODIFY(
    @JsonInput,
    '$.MyValue',
    ISNULL(@value, '')
);

select @JsonOutput

方法二：重新构造JSON（更灵活） #

如果需要对输出字段有更多控制，可以提取原JSON的字段，结合查询到的@value用FOR JSON PATH重新生成JSON：

declare @JsonInput nvarchar(MAX),
@JsonOutput nvarchar(MAX) 

set @JsonInput= N'{"Year":2022,"Surname":"Axel","Land":"Asia"}'

--define local variables
declare @name nvarchar(50), 
        @year int,
        @land nvarchar(50),
        @value nvarchar(50)
        

-- get json input data for dataset
set @name = JSON_VALUE(@JsonInput,'$.Surname')
set @year = JSON_VALUE(@JsonInput,'$.Year')
set @land = JSON_VALUE(@JsonInput,'$.Land')

--get  value
select @value = [Value] from  dbo.myTable  where ID='6' and [Year]=@year and Land=@land

-- 构造包含所有需要字段的JSON，WITHOUT_ARRAY_WRAPPER避免生成数组格式
SET @JsonOutput = (
    SELECT 
        @year AS Year,
        @name AS Surname,
        @land AS Land,
        ISNULL(@value, '') AS MyValue
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);

select @JsonOutput

存储过程化版本 #

如果需要封装成存储过程，可以将@JsonInput设为输入参数，@JsonOutput设为输出参数：

CREATE PROCEDURE dbo.ExtendJsonWithMyValue
    @JsonInput nvarchar(MAX),
    @JsonOutput nvarchar(MAX) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    declare @name nvarchar(50), 
            @year int,
            @land nvarchar(50),
            @value nvarchar(50)
    
    set @name = JSON_VALUE(@JsonInput,'$.Surname')
    set @year = JSON_VALUE(@JsonInput,'$.Year')
    set @land = JSON_VALUE(@JsonInput,'$.Land')

    select @value = [Value] from  dbo.myTable  where ID='6' and [Year]=@year and Land=@land

    SET @JsonOutput = JSON_MODIFY(
        @JsonInput,
        '$.MyValue',
        ISNULL(@value, '')
    );
END

内容的提问来源于stack exchange，提问作者atix91