Question 90063 in PostgreSQL Question posted in
The official documentation can be found here.

Aggregating table to json combined with references to other tables – Postgresql

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

3

Answers


  1. Chosen as BEST ANSWER
    0

    finally find the best solution

    select a.id     as id,
       a.status as status,
       t.bibi   as bibi
from a a

         left join (
    select t.b_a_id                 as b_a_id,
           JSON_AGG(ROW_TO_JSON(t)) as bibi
    from (
             select b.id     as b_id,
                    b.a_id   as b_a_id,
                    b.status as b_status
             from b b
             where b.status in (....)
         ) t
    group by t.b_a_id
) ap on ap.b_a_id = a.id
where (a.status in (...) OR ap.b_a_id is not null);

  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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}]|
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search