Node.js MongoDB的方面查询的Postgres类似功能

Demo at db<>fiddle:个

create table stores(id int,is_open boolean,popularity_count int,created_date date);
insert into stores values
(1,true,2,'yesterday'),
(2,true,7,now()-'1 month 2 weeks'::interval),
(3,true,8,'today allballs'),
(4,true,9,'epoch'),
(5,true,999,'-infinity'),
(6,false,0,'now'),
(7,false,0,'tomorrow'),
(8,false,0,'infinity'),
(9,true,99,'2023-10-30 13:13');

您可以使用常规聚合，在外部添加order by clause inside and a filter:

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;

请注意，正infinity和负infinity在PostgreSQL中是valid date and timestamp.

使用to_json()，你甚至可以得到你所展示的json示例.下面我使用它的jsonb版本to_jsonb()只是为了用jsonb_pretty()漂亮地打印:

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;