SQL如何根据JSON数组对象特定属性值条件更新对应属性

操作存储JSON格式的signal_key列时，如果需要精准定位entities数组内匹配value的对象、更新其属性，不能用LIKE做全局字符串匹配。
首先明确核心问题：entities是无固定下标的数组，你草稿里写死signal_key???的简单路径写法无法适配数组场景，必须使用对应数据库的原生JSON函数遍历定位元素后再更新，不能直接对整列做等值判断。

待处理JSON样例 #

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

不同数据库的可执行写法 #

MySQL 8.0+ 版本 #

MySQL 8.0及以上版本支持原生JSON操作函数，用JSON_SEARCH定位匹配value的元素路径，再用JSON_REPLACE更新对应type属性即可：

UPDATE signals
SET signal_key = JSON_REPLACE(
    signal_key,
    -- 定位value=AAAA的元素的type属性路径，替换值为ID
    REPLACE(JSON_UNQUOTE(JSON_SEARCH(signal_key, 'one', 'AAAA', null, '$.entities[*].value')), '.value', '.type'), 'ID',
    -- 定位value=CCC的元素的type属性路径，替换值为ID
    REPLACE(JSON_UNQUOTE(JSON_SEARCH(signal_key, 'one', 'CCC', null, '$.entities[*].value')), '.value', '.type'), 'ID'
)
WHERE 
  -- 只更新存在匹配value的行
  JSON_SEARCH(signal_key, 'one', 'AAAA', null, '$.entities[*].value') IS NOT NULL
  OR JSON_SEARCH(signal_key, 'one', 'CCC', null, '$.entities[*].value') IS NOT NULL;

说明：如果你的实际需求是像草稿里写的那样，把匹配到的value值本身替换为BBBB/DDD，只需要去掉上面REPLACE函数里替换.type路径的逻辑，把目标值从'ID'改成对应要替换的value值即可。你草稿里WHERE条件直接对signal_key做IN判断的写法是错误的，这种写法是把整列的JSON字符串和'AAAA'/'CCC'做等值比较，永远不会命中内部属性。

PostgreSQL 版本 #

PostgreSQL 对jsonb类型的支持更灵活，通过jsonb_array_elements展开数组匹配下标，再用jsonb_set更新属性：

UPDATE signals
SET signal_key = jsonb_set(
    jsonb_set(
        signal_key::jsonb,
        -- 匹配value=AAAA的元素下标，拼接type路径
        (SELECT ARRAY['entities', (idx-1)::text, 'type']
         FROM jsonb_array_elements(signal_key->'entities') WITH ORDINALITY arr(elem, idx)
         WHERE elem->>'value' = 'AAAA'),
        '"ID"'::jsonb,
        true
    ),
    -- 匹配value=CCC的元素下标，拼接type路径
    (SELECT ARRAY['entities', (idx-1)::text, 'type']
     FROM jsonb_array_elements(signal_key->'entities') WITH ORDINALITY arr(elem, idx)
     WHERE elem->>'value' = 'CCC'),
    '"ID"'::jsonb,
    true
)
WHERE signal_key::jsonb @? '$.entities[*] ? (@.value in ("AAAA", "CCC"))';

固定路径写法参考 #

如果是操作非数组的固定JSON属性，直接按以下规则写路径即可，不需要复杂遍历：

• MySQL 环境：用列名->>'$.属性层级'格式，例如：
  • 提取顶层signalType值：signal_key->>'$.signalType'
  • 提取entities数组第1个元素的value值（数组下标从0开始）：signal_key->>'$.entities[0].value'
• PostgreSQL 环境：用列名->>'属性名'逐层提取，例如：
  • 提取顶层signalType值：signal_key->>'signalType'
  • 提取entities数组第1个元素的value值：signal_key->'entities'->0->>'value'

数组因为元素位置不固定，不能用固定下标路径，必须遍历匹配后操作。

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