how to get json result to fixed number by grouping in postgresql?-CodePudding

I want to get expected results to one query execution. the "where" clause should one value. so I can't use this query as sub-query of main-query.

select t.id 
from(
  select row_number() over(order by id) as row, id 
  from tt
) t 
where t.row%4 = 0;

could you tell me how to do it?

Sample sql query:

with tt (id, name) as (
values
  (1,'hello'),
  (4,'good'),
  (7,'world'),
  (9,'happy'),
  (10,'funny'),
  (12,'stack'),
  (15,'queue'),
  (18,'map'),
  (19,'list'),
  (23,'code')
)
select json_agg(tmp) as results 
from (
  select id, name 
  from tt 
  where id > ? 
  order by id asc 
  fetch first 4 row only
) tmp;
-- bind ? : 0, 9, 18

Expected results:

results
[{"id":1,"name":"hello"},{"id":4,"name":"good"},{"id":7,"name":"world"}, {"id":9,"name":"happy"}]
[{"id":10,"name":"funny"},{"id":12,"name":"stack"},{"id":15,"name":"queue"},{"id":18,"name":"map"}]
[{"id":19,"name":"list"},{"id":23,"name":"code"}]

CodePudding user response：

demo: db<>fiddle

SELECT
    json_agg(json_obj)                               -- 4
FROM (
    SELECT 
        *,
        row_to_json(tt.*) as json_obj,               -- 1
        row_number() OVER (ORDER BY id) - 1 as idx   -- 2
    FROM tt
) s
GROUP BY idx / 4                                     -- 3

Convert your records into JSON objects
Create a row count using the row_number() window function
Create groups of 4 by using integer division
Aggregate these groups into a JSON array