Skip to content

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

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 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 (
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:

[{“id”:1,”name”:”hello”},{“id”:4,”name”:”good”},{“id”:7,”name”:”world”}, {“id”:9,”name”:”happy”}]


demo: db<>fiddle

    json_agg(json_obj)                               -- 4
        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
  1. Convert your records into JSON objects
  2. Create a row count using the row_number() window function
  3. Create groups of 4 by using integer division
  4. Aggregate these groups into a JSON array