sql - Postgres The plpgsql aggregate function filters the length of each group

Question

For plpgsql aggregate function help, not sure whether it can be realized. Thanks in advance for your help

Table
_id   group_id   content    num   len
0        2          tab      1     3
1        2          name     2     4
2        1          tag      1     3
3        1          bag      2     3
4        1          a        3     1
5        2          b        3     1
6        1          bo       4     2 
7        2          an       4     2

I want to implement an aggregation function to aggregate according to group_id, and num is processed in sorted order, and then judge in the function to skip if len is less than or equal to 2, and then return the data of the specified length after each aggregation.

example:
with sorted_table as(select * from Table order by num)
select my_func(content, len, 2(required_num)) from sorted_table group by group_id;

expect result

_id   group_id   content    num   len
0        2          tab      1     3
1        2          name     2     4
2        1          tag      1     3
3        1          bag      2     3

for example, need to sort the top 10 (required_num) in each group, sort according to the num of each group, and compare the contents of the top 10 in turn. If the similarity is too high(i can use select similarity judge), filter out, and so on to reach 10 per group Claim. It may also be this

group_id  result
2         [{"num":1,"content":"tab","len":3,"_id":0},{"num":2,"content":"name","len":4,"_id":1}]
1         [{"num":1,"content":"tag","len":3,"_id":2},{"num":2,"content":"bag","len":3,"_id":3}]

question from:https://stackoverflow.com/questions/65903366/postgres-the-plpgsql-aggregate-function-filters-the-length-of-each-group

Answer 1

As far as I understand the question, you don't really need the custom aggregate:

select group_id, 
       jsonb_agg(t) filter (where len <= 2) as result
from the_table t
group by group_id;