如何在MySQL中无需指定键，通过JSON值整词过滤行？

算力阿峰

2026-6-17

在MySQL中未知JSON键时按值的完整单词过滤行

可以实现，不需要硬编码所有JSON键，以下是几种可行方案：

问题分析

你尝试用name->>'$.*'获取所有JSON值，但它返回的是JSON数组的字符串形式（例如["Konzum Zagreb Tower", "Konzum in Zagreb Tower"]），包裹值的双引号和数组分隔符会干扰INSTR对完整单词的匹配，导致查询失效。

方案1：用JSON_TABLE展开所有JSON值（推荐）

将JSON对象的所有值转换为独立行，再逐行匹配完整单词，最后去重即可：

SELECT DISTINCT p.id, p.name
FROM provider p
JOIN JSON_TABLE(
    JSON_EXTRACT(p.name, '$.*'),
    '$[*]' COLUMNS (value_str VARCHAR(255) PATH '$')
) jt
-- 使用正则匹配完整单词，比INSTR更可靠
WHERE LOWER(value_str) REGEXP '[[:<:]]zagreb[[:>:]]';

说明：

JSON_EXTRACT(p.name, '$.*')提取所有JSON值组成数组
JSON_TABLE将数组拆分为多行，每行对应一个JSON值
[[:<:]]和[[:>:]]是MySQL正则的单词边界锚点，确保匹配的是完整单词（不会误匹配zagrebx或xzagreb）
DISTINCT避免同一行因多个值匹配而重复输出

如果偏好INSTR的写法，也可以替换WHERE条件为：

WHERE INSTR(CONCAT(' ', LOWER(value_str), ' '), CONCAT(' ', 'zagreb', ' ')) > 0;

方案2：用JSON_SEARCH直接匹配

利用JSON_SEARCH的模糊匹配能力，结合多种边界情况判断值中是否存在目标完整单词：

SELECT id, name
FROM provider
WHERE 
    JSON_SEARCH(LOWER(name), 'one', '% zagreb %') IS NOT NULL
    OR JSON_SEARCH(LOWER(name), 'one', 'zagreb %') IS NOT NULL
    OR JSON_SEARCH(LOWER(name), 'one', '% zagreb') IS NOT NULL
    OR JSON_SEARCH(LOWER(name), 'one', 'zagreb') IS NOT NULL;