这是使用表B中的状态筛选器进行的基本查询（例如状态1，3）

select 
a.id, a.status, b.id b_id, b.status b_status
from a
join b
on a.id = b.a_id
where b.status in (1,3)

id|status|b_id|b_status|
--+------+----+--------+
 1|     1|   2|       3|
 1|     1|   1|       1|

现在只需要对前两列进行分组并聚合JSON数组。
json_agg和json_build_object是解决方案

with tab as (
select 
a.id, a.status, b.id b_id, b.status b_status
from a
join b
on a.id = b.a_id
where b.status in (1,3)
)
select tab.id, tab.status,
json_agg(
 json_build_object('id', tab.b_id, 'status', tab.b_status,'a_id',tab.id) 
 ORDER BY b_id) as arrayjson
from tab
group by 1,2
order by 1,2

id|status|arrayjson                                                                   |
--+------+----------------------------------------------------------------------------+
 1|     1|[{"id" : 1, "status" : 1, "a_id" : 1}, {"id" : 2, "status" : 3, "a_id" : 1}]|

给定一组查找状态值，您可以用途：

• GENERATE_SERIES，生成可能的seek_status值
• JSON_BUILD_OBJECT，从B表开始构建json
• JSON_AGG，用于聚合json
• WHERE v.seek_status IN (1,3)，更改所需的seek_status
• ORDER BY A.status DESC LIMIT 1，以获取所有输出记录中可能的最高状态

SELECT A.*,
       CASE WHEN MAX(B.status) IS NOT NULL
            THEN JSON_AGG(JSON_BUILD_OBJECT('id'    , B.id,
                                            'status', B.status,
                                            'a_id'  , B.a_id   ))
            ELSE '[]' END AS arrayjson
FROM      GENERATE_SERIES(1,5) v(seek_status)
LEFT JOIN B ON v.seek_status = B.status
LEFT JOIN A ON v.seek_status >= A.status
WHERE v.seek_status IN (1,3)
GROUP BY A.id, A.status, B.status
ORDER BY A.status DESC
LIMIT 1

查看here演示。