SQL-基于特定对象属性替换值

Question

我正在尝试在SQL中编写有条件的更新语句，该语句查看一系列对象，并根据另一个对象的属性更新一个对象属性。以下是我要更新的signal_key列中对象的示例。

{
  "entities": [
    {"type": "MERCHANT", "value": "AAAA"}, 
    {"type": "MERCHANT", "value": "CCC"}], 
  "signalType": "TRANSACTION", 
  "signalVersion": "0"
}

基于“ value”字符串，我想将“类型”从商家更新为id。我有没有办法专门检查值，而不是使用像之类的东西，它会在整个对象中寻找字符串比较？下面，signal_key ???指示我要问的查询的部分。

UPDATE signals
SET signal_key??? =  CASE
        WHEN signal_key??? = 'AAAA' THEN 'BBBB'
        WHEN signal_key??? = 'CCC' THEN 'DDD'
        ELSE signal_key???
    END
WHERE signal_key??? IN ('AAAA', 'CCC')

Answer 1

基于值字符串的长度

您可以使用len（my_field）检查长度

：

UPDATE some_table
SET some_field = CASE
        WHEN len(another_field) = 4 THEN 'BBBB'
        WHEN len(another_field) = 3 THEN 'DDD'
        ELSE 0
    END
WHERE 1=1

编辑：基于相关数据更新：

首先创建一个CTE（常见表达式，使用带有），其中包含每个项目，其中包含相关项目的长度。在下面的示例中，CTE包含每个项目ID，其中包含相关项目的计数（或“长度”）。

然后发出更新语句，该语句使用与要更新的表连接的长度表。

with lengths (id, length) as (
    select t1.id, count(related.id)
    from test_table_1 t1
    inner join test_table_related_data related on related.parentId = t1.id
    group by t1.id
)
update t1
set t1.name = case when l.length = 1 then 'one' else 'a lot!!!' end
from test_table_1 t1
inner join lengths l on l.id = t1.id

Answer 2

要解析JSON，您应该使用SQL Server的JSON功能。

您需要在相关的子查询中使用OpenJson以找到正确的修改索引。

如果您只想修改每行单个值，则可以这样做。

UPDATE s
SET signal_key = JSON_MODIFY(
        s.signal_key,
        '$.entities[' + j.[key] + '].value',
        'BBBB')
FROM signals s
CROSS APPLY (
    SELECT TOP (1) j.[key]
    FROM OPENJSON(s.signal_key, '$.entities') j
    WHERE JSON_VALUE(j.value, '$.value') = 'AAAA'
) j;

否则您需要重建JSON阵列

UPDATE s
SET signal_key = JSON_MODIFY(
        s.signal_key,
        '$.entities',
        j.entities)
FROM signals s
CROSS APPLY (
    SELECT
      j.type,
      value = CASE j.value
                WHEN 'AAAA' THEN 'BBBB'
                WHEN 'CCC' THEN 'DDD'
                ELSE j.value
              END
    FROM OPENJSON(s.signal_key, '$.entities')
      WITH (
        type varchar(100),
        value varchar(100)
      ) j
    FOR JSON PATH
) j(entities);