PostgreSQL如何查询含任意指定词的记录并返回JSON格式结果

WHERE IN (...)仅支持字段值和列表项完全相等的精确匹配，无法实现「字段文本包含列表中任意关键词」的模糊匹配需求，以下是可直接落地的写法，覆盖你需要的两种输出格式。

输出格式2：带匹配统计的表格 #

不同数据库的适配写法如下：

MySQL 写法 #

通过LIKE判断关键词命中情况，拼接命中标签、统计命中数：

SELECT
    text,
    CONCAT_WS(',',
        IF(text LIKE '%today%', 'today', NULL),
        IF(text LIKE '%likes%', 'likes', NULL),
        IF(text LIKE '%eat%', 'eat', NULL)
    ) AS tag,
    (text LIKE '%today%') + (text LIKE '%likes%') + (text LIKE '%eat%') AS tag_counts
FROM tbl
WHERE
    text LIKE '%today%'
    OR text LIKE '%likes%'
    OR text LIKE '%eat%';

注意：如果需要严格匹配独立单词（避免把eaten这类包含eat子串的长单词误判为命中），MySQL 8.0+版本可以用REGEXP_LIKE(text, CONCAT('[[:<:]]', kw, '[[:>:]]'))替换LIKE匹配规则。

PostgreSQL 写法 #

用数组特性简化多关键词判断逻辑，不需要重复写匹配条件：

WITH keyword_list AS (
    SELECT ARRAY['today','likes','eat']::text[] AS keywords
)
SELECT
    t.text,
    ARRAY_TO_STRING(
        ARRAY(SELECT kw FROM UNNEST(k.keywords) kw WHERE t.text LIKE '%' || kw || '%'),
        ', '
    ) AS tag,
    CARDINALITY(ARRAY(SELECT kw FROM UNNEST(k.keywords) kw WHERE t.text LIKE '%' || kw || '%')) AS tag_counts
FROM tbl t, keyword_list k
WHERE t.text LIKE ANY(ARRAY(SELECT '%' || kw || '%' FROM UNNEST(k.keywords) kw));

SQLite 写法 #

兼容低版本SQLite的通用写法：

SELECT
    text,
    TRIM(
        COALESCE(CASE WHEN text LIKE '%today%' THEN ',today' ELSE '' END, '') ||
        COALESCE(CASE WHEN text LIKE '%likes%' THEN ',likes' ELSE '' END, '') ||
        COALESCE(CASE WHEN text LIKE '%eat%' THEN ',eat' ELSE '' END, ''),
        ','
    ) AS tag,
    (text LIKE '%today%') + (text LIKE '%likes%') + (text LIKE '%eat%') AS tag_counts
FROM tbl
WHERE text LIKE '%today%' OR text LIKE '%likes%' OR text LIKE '%eat%';

输出格式1：JSON结构结果 #

MySQL 写法 #

用内置JSON函数构造结果，单标签直接返回字符串，多标签返回数组：

SELECT JSON_OBJECT(
    'id', id, -- 替换为你表实际的主键字段名
    'text', text,
    'tag',
    CASE
        WHEN (text LIKE '%today%') + (text LIKE '%likes%') + (text LIKE '%eat%') = 1
        THEN (
            CASE WHEN text LIKE '%today%' THEN 'today'
                 WHEN text LIKE '%likes%' THEN 'likes'
                 WHEN text LIKE '%eat%' THEN 'eat' END
        )
        ELSE JSON_ARRAY(
            IF(text LIKE '%today%', 'today', NULL),
            IF(text LIKE '%likes%', 'likes', NULL),
            IF(text LIKE '%eat%', 'eat', NULL)
        )
    END
) AS json_result
FROM tbl
WHERE text LIKE '%today%' OR text LIKE '%likes%' OR text LIKE '%eat%';

PostgreSQL 写法 #

WITH keyword_list AS (
    SELECT ARRAY['today','likes','eat']::text[] AS keywords
)
SELECT JSON_BUILD_OBJECT(
    'id', t.id,
    'text', t.text,
    'tag', CASE
        WHEN CARDINALITY(matched_kws) = 1 THEN matched_kws[1]
        ELSE TO_JSONB(matched_kws)
    END
) AS json_result
FROM (
    SELECT t.*, ARRAY(SELECT kw FROM UNNEST(k.keywords) kw WHERE t.text LIKE '%' || kw || '%') AS matched_kws
    FROM tbl t, keyword_list k
) t
WHERE CARDINALITY(matched_kws) > 0;

优化提示 #

• 表数据量超过10万行时，不要用带前置%的LIKE匹配，建议给text字段建立全文索引，用数据库内置全文检索语法（MySQL的MATCH() AGAINST()、PostgreSQL的tsvector/tsquery）替换LIKE，查询效率可提升10倍以上。
• 关键词列表超过10个时，不要硬编码在SQL语句中，可以单独建关键词配置表做关联匹配，后续维护关键词不需要改SQL逻辑。

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