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”}] |
Advertisement
Answer
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