Table A:

id	status
1	1
2	4

Table B:

id	status	a_id
1	1	1
2	3	1
3	5	2

Table A (
id int,
status int);

Table B(
id int,
status int,
a_id int foreignt key reference A
);

How to make query that return such output, when I seek status in (1,3)?

id	status	arrayjson
1	1	[{id=1,status=1,a_id=1},{id=2,status=3,a_id=1}]

If I seek status in ( 3 ), it should return:

id	status	arrayjson
1	1	[{id=2,status=3,a_id=1}]

If I seek status in ( 4 ), it should return:

id	status	arrayjson
2	4	[]

If I seek status in ( 5 ) it should return:

id	status	arrayjson
2	4	[{id=2,status=4,a_id=2}]

CodePudding user response：

This is you basic query with the filter on status from table B (example for status 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|

Now you need only to group on the first two columns and aggregate the JSON array.

json_agg and json_build_object are the solution

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}]|

CodePudding user response：

Given your set of seek status values, you can use:

• GENERATE_SERIES, to generate your possible seek_status values
• JSON_BUILD_OBJECT, to build your json beginning from your B table
• JSON_AGG, to aggregate your json
• WHERE v.seek_status IN (1,3), to change the seek_status you need
• ORDER BY A.status DESC LIMIT 1, to get the highest status possible among all output records

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

Check the demo here.