json - 从 sql 列值更新 json 值

Question

使用 SQL Server，我想获取列数据并将其复制到 json 对象列中

我正在使用 SQL Server 查询列和 json 数据。我想要做的是将 ename 列中的数据复制到下面代码中的 fieldvalue 列。如果我可以使用 SQL 来完成，那就太好了。

SELECT 
    a.id, a.ssn, a.ename, p.CaptionName, p.FieldName, p.FieldType, p.FieldValue
FROM 
    tablename as a
CROSS APPLY 
    OPENJSON (details)
        WITH (CaptionName NVARCHAR(100),
              FieldName NVARCHAR(100),
              FieldType NVARCHAR(15),
              FieldValue NVARCHAR(50)) AS P
WHERE
    p.captionname = 'txtEname' 
    AND a.ssn = '000-00-0000'

我在详情栏的json字符串

[{"CaptionName":"txtEname","FieldName":null,"FieldType":null,"FieldValue":""}]

我真的不太擅长使用 sql，而这正是我想使用的。将数据复制到 json 对象后，我将删除 ename 列。

Answer 1

更新 2019-07-11

这是一个修改后的解决方案，适用于 JSON 中有多个值的情况:https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=1fde45dfb604b2d5540c56f6c17a822d

update a
set details = JSON_MODIFY(details, '$[' + x.[key] + '].FieldValue', ename)
from dbo.tblUissAssignments a
CROSS APPLY OPENJSON (details, '$') x
CROSS APPLY OPENJSON (x.Value)
    WITH (CaptionName NVARCHAR(100),
        FieldName NVARCHAR(100),
        FieldType NVARCHAR(15),
        FieldValue NVARCHAR(50)) AS P
WHERE a.ssn = '000-00-0000'
and p.CaptionName = 'txtEname'    

这与我原来的回答相似(见下文)。然而:

• 我们现在有 2 个cross apply 语句。第一个用于将 JSON 数组拆分为元素，因此我们得到一个键(索引)和值(作为字符串的 JSON 对象)，如此处记录:https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-2017#path
• 第二个做你原来的 CROSS APPLY 所做的，只作用于单个数组元素。
• 我们使用第一个 cross apply 返回的 [key] 来定位我们希望在我们的 JSON_MODIFY 中更新的数组中的项目> 声明。

注意:如果您的 JSON 数组可能包含多个需要更新的对象，我能想到的最佳解决方案是将上述语句放入循环中；因为 1 次更新只会更新给定 JSON 上的 1 个索引。这是一个例子:https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=120d2ac7dd3a024e5e503a5f64b0089e

declare @doWhileTrueFlag bit = 1

while (@doWhileTrueFlag = 1)
begin

    update a
    set details = JSON_MODIFY(details, '$[' + x.[key] + '].FieldValue', ename)
    from dbo.tblUissAssignments a
    CROSS APPLY OPENJSON (details, '$') x
    CROSS APPLY OPENJSON (x.Value)
        WITH (CaptionName NVARCHAR(100),
            FieldName NVARCHAR(100),
            FieldType NVARCHAR(15),
            FieldValue NVARCHAR(50)) AS P
    WHERE a.ssn = '000-00-0000'
    and p.CaptionName = 'txtEname'    
    and p.FieldValue != ename --if it's already got the correct value, don't update it again

    set @doWhileTrueFlag = case when @@RowCount > 0 then 1 else 0 end

end

---

原始答案

试试这个:https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b7b4d075cac6cd46239561ddb992ac90

update a
set details = JSON_MODIFY(details, '$[0].FieldValue', ename)
from dbo.tblUissAssignments a
cross apply
    OPENJSON (details)
        WITH (CaptionName NVARCHAR(100),
              FieldName NVARCHAR(100),
              FieldType NVARCHAR(15),
              FieldValue NVARCHAR(50)) AS P
where a.ssn = '000-00-0000'
and p.captionname = 'txtEname' 

有关 JSON_MODIFY 方法的更多信息:https://learn.microsoft.com/en-us/sql/t-sql/functions/json-modify-transact-sql?view=sql-server-2017

微妙的一点是，您正在更新一个包含 json 对象的 json 数组；不是一个单一的对象。为此，您必须在根元素上包含索引。如果您不熟悉 JsonPath，请参阅这篇文章以获取一些有用的信息:https://support.smartbear.com/alertsite/docs/monitors/api/endpoint/jsonpath.html

---

对于数组中有多个项的场景，理想情况下我们会使用过滤表达式，例如:

update a
set details = JSON_MODIFY(details, '$[?(@.CaptionName == ''txtEname'')].FieldValue', ename)
from dbo.tblUissAssignments a
where a.ssn = '000-00-0000'

遗憾的是 MS SQL 尚不支持这些(请参阅这篇优秀的文章:https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016)

因此，我认为我们需要应用一个讨厌的 hack。我想到了两种这样的方法:

• 实现循环遍历所有匹配项
• 将 JSON 转换为其他类型，然后再转换回 JSON

我会考虑这些/是否有更清洁的东西，因为目前两者都不太舒服......