PostgreSQL获取JSON数组匹配条件的计数并实现结果排序

核心方案：展开数组+统计匹配数 #

要统计supers数组中匹配指定keyword的name字段数量，并以此筛选、排序，你需要先将JSON数组展开为行集，再针对匹配条件计数。以下是两种可行的SQL写法：

方法1：使用LATERAL JOIN展开数组（推荐，性能更优）

SELECT 
    ac.*,
    COUNT(s.name) AS match_count
FROM 
    all_cards ac
JOIN LATERAL 
    jsonb_to_recordset(ac.supers::jsonb) AS s(name text, category text) 
    ON s.name = '你的目标keyword'
GROUP BY 
    ac.id -- 替换为all_cards表的主键字段，PostgreSQL 10+版本可直接用ac.*
HAVING 
    COUNT(s.name) >= 8
ORDER BY 
    match_count DESC;

关键说明：

• jsonb_to_recordset(ac.supers::jsonb)：将JSON数组转换为结构化行集，明确指定数组元素的字段（name和category）
• JOIN LATERAL：为all_cards的每一行关联其展开后的匹配行，仅保留name符合条件的条目
• COUNT(s.name)：统计当前行中匹配的数组元素数量
• HAVING子句：筛选匹配数≥8的行
• ORDER BY match_count DESC：按匹配计数从高到低排序

如果你的supers列是JSON类型而非JSONB，将jsonb_to_recordset替换为json_to_recordset即可。

方法2：使用相关子查询计算匹配数

SELECT 
    *,
    (SELECT COUNT(*) 
     FROM jsonb_array_elements(supers::jsonb) AS elem
     WHERE (elem->>'name') = '你的目标keyword') AS match_count
FROM 
    all_cards
WHERE 
    (SELECT COUNT(*) 
     FROM jsonb_array_elements(supers::jsonb) AS elem
     WHERE (elem->>'name') = '你的目标keyword') >= 8
ORDER BY 
    match_count DESC;

关键说明：

• 内层子查询通过jsonb_array_elements展开数组，筛选name匹配的元素并计数
• 外层查询利用这个计数结果做筛选和排序，无需分组操作

为什么jsonb_array_length无法解决问题？ #

jsonb_array_length仅能统计整个JSON数组的总长度，无法过滤匹配条件的元素后计数，因此无法满足你的需求。

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