PostgreSQL实现MongoDB $facet查询功能的方法

当然可以。PostgreSQL完全能实现MongoDB里$facet的核心功能——基于同一份筛选后的数据集，生成多个独立处理的分支结果，最后组装成指定结构的JSON。

针对你提到的stores表需求，这里直接给出可运行的SQL实现：

WITH filtered_stores AS (
    -- 先筛选出所有营业中的门店，后续两个分支复用这个数据集
    SELECT *
    FROM stores
    WHERE open = true
)
SELECT json_build_object(
    -- 第一个分支：人气值大于5的门店，按人气降序排列
    'most popular', (
        SELECT json_agg(row_to_json(store))
        FROM filtered_stores store
        WHERE store.popularityCount > 5
        ORDER BY store.popularityCount DESC
    ),
    -- 第二个分支：近5天新增的门店，按创建日期降序排列
    'newly added', (
        SELECT json_agg(row_to_json(store))
        FROM filtered_stores store
        WHERE store.createdDate >= CURRENT_DATE - INTERVAL '5 days'
        ORDER BY store.createdDate DESC
    )
) AS facet_result;

关键逻辑说明 #

• CTE复用数据集：filtered_stores作为公共表达式，只执行一次筛选操作，两个分支都基于这份结果处理，避免重复扫描表，效率和MongoDB的$facet一致。
• JSON转换与聚合：
  • row_to_json(store)把单条门店记录转换成JSON对象；
  • json_agg()将多条JSON对象聚合为JSON数组；
• 组装最终结构：json_build_object()把两个分支的结果按指定的键名（most popular、newly added）组装成最终的JSON输出，结构和你需要的完全匹配。

可选优化 #

如果不需要返回门店的所有字段，可以手动指定要包含的字段，比如把row_to_json(store)替换成：

json_build_object(
    'id', store.id,
    'name', store.name,
    'popularityCount', store.popularityCount
)

这样能减少返回的JSON体积，提升传输效率。

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