TSQL 如何合并两个 Json 数组

Question

如何将新的数组元素合并到现有的 Json 属性中？我们的两个 json 对象如下所示，我们希望将第二个 json 对象的项附加到第一个 json 对象。(查看所需结果(

有没有办法用JSON_MODIFY实现这一目标？Microsoft 文档并没有真正显示将多个元素合并到现有数组中的任何示例。只有一个元素。但是我们有一个需要合并的多个元素的列表。

编辑：JSON_MODIFY(@json1，'追加 $.Items'， JSON_QUERY(@json2(( 似乎创建新的数组括号，而不是将项目合并到数组中。

JSON 对象 1：

DECLARE @json1 NVARCHAR(MAX) = '{
    "id": 1,
    "Items": [
        {
            "id": 1,
            "name" : "Item #1"
        },
        {
            "id": 2,
            "name" : "Item #2"
        }
    ]
}'

Json 对象 2：

DECLARE @json2 NVARCHAR(MAX)='{
    "Items": [
        {
            "id": 3,
            "name": "Item #3"
        },
        {
            "id": 4,
            "name": "Item #4"
        }
    ]
}'

期望的结果：

{
    "id": 1,
    "Items": [
        {
            "id": 1,
            "name": "Item #1"
        },
        {
            "id": 2,
            "name": "Item #2"
        },
        {
            "id": 3,
            "name": "Item #3"
        },
        {
            "id": 4,
            "name": "Item #4"
        }
    ]
}

Answer 1

这很麻烦，但可以通过SQL Server构建的JSON支持来实现。

首先，设置适当的示例数据(请在以后的问题中保存此步骤(：

DECLARE @Json1 nvarchar(max) = 
'{
    "id": 1,
    "Items": [
        {
            "id": 1,
            "name" : "Item #1"
        },
        {
            "id": 2,
            "name" : "Item #2"
        }
    ]
}',
@Json2 nvarchar(max) = 
'{
    "Items": [
        {
            "id": 3,
            "name": "Item #3"
        },
        {
            "id": 4,
            "name": "Item #4"
        }
    ]
}';

然后，使用公用表表达式包装一个包含每个变量的openjson和json_query的union all查询：

With cteArray as
(
    SELECT *
    FROM OPENJSON(JSON_QUERY(@Json1, '$.Items'))
    WITH(
        Id int '$.id',
        Name varchar(100) '$.name'
    )
    UNION ALL 
    SELECT *
    FROM OPENJSON(JSON_QUERY(@Json2, '$.Items'))
    WITH(
        Id int '$.id',
        Name varchar(100) '$.name'
    )
)

该联合所有查询的结果如下：

Id      Name
1       Item #1
2       Item #2
3       Item #3
4       Item #4

然后，使用 json_value 从第一个 json 中选择id，并添加一个子查询以从带有 for json path 的 cte 中选择所有内容。添加另一个for json path并为外部查询指定without_array_wrapper：

SELECT JSON_VALUE(@Json1, '$.id') As id,
        (
            SELECT * 
            FROM cteArray
            FOR JSON PATH
        ) as Items
FOR JSON PATH,
WITHOUT_ARRAY_WRAPPER

最终结果：

{
    "id": "1",
    "Items": [{
            "Id": 1,
            "Name": "Item #1"
        }, {
            "Id": 2,
            "Name": "Item #2"
        }, {
            "Id": 3,
            "Name": "Item #3"
        }, {
            "Id": 4,
            "Name": "Item #4"
        }
    ]
}

您可以在 Db<>Fiddle 上观看现场演示

Answer 2

您的问题可以通过以下方式解决

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

我们在尝试合并MS SQL中的JSON时遇到了类似的问题。我们还希望它是递归的，并允许我们为"union"、"concat"和"replace"等数组定义策略。

我们的 JSON 操作解决方案(如合并、JSON 路径表达式等(是开源的，可用 @ Github

随意使用，评论和贡献，以便我们可以进一步改进MS SQL的JSON方法。

Answer 3

不漂亮，但这将合并文本和对象元素。 我敢肯定它不是防弹的。 仅作为潜在解决方案提供。

Declare @json1 nvarchar(max) = '{"id":1,"messages":[{"type":"Info","text":"message1"},{"type":"Info","text":"message2"}]}'
Declare @json2 nvarchar(max) = '{"id":1,"messages":["justPlanText",{"type":"Info","text":"message3"},{"type":"Info","text":"message4"}]}'
select @json1 = case 
                when isjson(m.value) = 1 then
                  json_modify(@json1,'append $.messages',json_query(m.value))
                else 
                  json_modify(@json1,'append $.messages',m.value)
                end
from openjson(json_query(@json2,'$.messages')) m
select @json1

@json1的结果：

{"id":1,"messages":[{"type":"Info","text":"message1"},{"type":"Info","text":"message2"},"justPlanText",{"type":"Info","text":"message3"},{"type":"Info","text":"message4"}]}