如何用SQL正则查询JSON字段中指定value值是否存在？

针对你的需求，优先推荐使用数据库原生的JSON处理函数（比正则更可靠），也可以用正则表达式实现，具体方法如下：

一、原生JSON函数（推荐） #

不同数据库的JSON函数语法略有差异，以下是主流数据库的实现示例：

MySQL #

1. 使用JSON_CONTAINS直接匹配数组中的对象：

SELECT * FROM socialMedia 
WHERE JSON_CONTAINS(your_json_column, '{"value":"person1"}') 
   OR JSON_CONTAINS(your_json_column, '{"value":"person2"}');

（替换your_json_column为实际的JSON列名）

2. 若需要匹配多个值，也可通过JSON_TABLE将JSON数组转为行数据后查询：

SELECT s.*
FROM socialMedia s
JOIN JSON_TABLE(
    s.your_json_column,
    '$[*]' COLUMNS(
        value_str VARCHAR(255) PATH '$.value'
    )
) jt
WHERE jt.value_str IN ('person1', 'person2');

PostgreSQL #

1. 使用@>操作符判断JSON数组是否包含指定对象：

SELECT * FROM socialMedia 
WHERE your_json_column::jsonb @> '[{"value":"person1"}]' 
   OR your_json_column::jsonb @> '[{"value":"person2"}]';

2. 展开JSON数组后匹配值：

SELECT s.*
FROM socialMedia s,
     jsonb_array_elements(s.your_json_column::jsonb) j
WHERE j->>'value' IN ('person1', 'person2');

二、正则表达式方法（不推荐） #

虽然可以通过正则匹配文本形式的JSON，但JSON格式可能存在空格、换行、转义字符等变化，容易出现误匹配或漏匹配的情况。如果一定要用，示例如下：

MySQL #

SELECT * FROM socialMedia 
WHERE your_json_column REGEXP '"value":"person1"' 
   OR your_json_column REGEXP '"value":"person2"';

PostgreSQL #

SELECT * FROM socialMedia 
WHERE your_json_column::text ~ '"value":"person1"' 
   OR your_json_column::text ~ '"value":"person2"';

注意：正则方法仅适用于JSON格式固定、无特殊字符的场景，优先选择原生JSON函数保证准确性。

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