如何读取匹配查询参数的JSON对象并清理视频URL参数

一、正确筛选目标数据行 #

你当前的SQL存在两处问题：一是JSON字段中带横杠的键名访问方式错误，二是LIKE语法格式不符合规范。以下是主流数据库的正确写法：

PostgreSQL（JSON/JSONB类型） #

若metadata为JSONB或JSON类型，需用->>操作符提取video-url的文本值后再匹配：

SELECT *
FROM content
WHERE metadata ->> 'video-url' LIKE '%pubtool=%';

如果要避免误匹配URL路径中的相似字符串，可改用正则表达式精准匹配查询参数：

SELECT *
FROM content
WHERE metadata ->> 'video-url' ~ '\?pubtool=|pubtool=.*&';

MySQL（JSON类型） #

MySQL中可通过->>简化操作符提取JSON字段的文本值：

SELECT *
FROM content
WHERE metadata->>'$.video-url' LIKE '%pubtool=%';

也可使用完整函数写法：

SELECT *
FROM content
WHERE JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.video-url')) LIKE '%pubtool=%';

二、移除pubtool查询参数 #

筛选出目标行后，可通过以下SQL修改video-url字段，移除指定查询参数：

PostgreSQL实现 #

用正则表达式拆分并重组URL：

UPDATE content
SET metadata = jsonb_set(
    metadata,
    '{video-url}',
    to_jsonb(
        CASE
            -- 处理包含多个参数的URL
            WHEN metadata ->> 'video-url' LIKE '%&pubtool=%' THEN regexp_replace(metadata ->> 'video-url', '&pubtool=[^&]*', '', 'g')
            -- 处理仅含pubtool一个参数的URL
            WHEN metadata ->> 'video-url' LIKE '%?pubtool=%' THEN regexp_replace(metadata ->> 'video-url', '\?pubtool=[^&]*', '', 'g')
            ELSE metadata ->> 'video-url'
        END
    )
)
WHERE metadata ->> 'video-url' LIKE '%pubtool=%';

MySQL实现 #

利用字符串函数处理URL结构：

UPDATE content
SET metadata = JSON_SET(
    metadata,
    '$.video-url',
    CASE
        WHEN metadata->>'$.video-url' LIKE '%&pubtool=%' THEN REPLACE(metadata->>'$.video-url', CONCAT('&pubtool=', SUBSTRING_INDEX(SUBSTRING_INDEX(metadata->>'$.video-url', '&pubtool=', -1), '&', 1)), '')
        WHEN metadata->>'$.video-url' LIKE '%?pubtool=%' THEN LEFT(metadata->>'$.video-url', LOCATE('?pubtool=', metadata->>'$.video-url') - 1)
        ELSE metadata->>'$.video-url'
    END
)
WHERE metadata->>'$.video-url' LIKE '%pubtool=%';

注意事项 #

• 执行更新操作前，建议先用SELECT语句验证处理后的结果是否符合预期
• 若需保留原数据，可通过SELECT生成处理后的结果集，而非直接执行UPDATE

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