Postgres 等效于 mongodb 的 facet 查询

为此，您可以使用常规聚合，在内部添加 order by 子句，在外部添加过滤器： db<>fiddle 上的演示：

select array_agg(stores order by popularity_count)
          filter(where popularity_count>5) 
          as "most popular"
      ,array_agg(stores order by created_date)
          filter(where now()-'5 days'::interval <= created_date) 
          as "newly added"
from stores where is_open;

请注意，正和负是 PostgreSQL 中的有效日期和时间戳。infinity

使用 to_json（），您甚至可以获得您显示的确切 json 示例。下面我使用它的版本只是为了用 jsonb_pretty（） 进行漂亮的打印：jsonbto_jsonb()

select jsonb_pretty(to_jsonb(payload)) 
from (
    select array_agg(stores order by popularity_count)
              filter(where popularity_count>5) 
              as "most popular"
          ,array_agg(stores order by created_date)
              filter(where now()-'5 days'::interval <= created_date) 
              as "newly added"
    from stores where is_open
) AS payload;