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

Postgresql – sql return json object by status from two tables with different number of rows

I am using PostgreSQL 9.5

I have two table:

A

ID | isStatusA | IsStatusB | IsStatusC
0  | true      | false     | true

A table has only 1 row for data i need. Also i have B table with:

ID | status | a_id
0  | A      | 0
0  | C      | 0

When i wrote my select with "from A inner join B on a.id = b.a_id" that i get 2 rows. I have to get only one row (or json object) with checking that is that true/false in table A and is status present in table B. To return true i have to check both conditions.

I want {A: true, B: false, C:true} or something similar with using pivot.

Tags:

2

Answers


  1. 0

    Using PostgreSQL ARRAY_AGG() function along with GROUP BY to "denormalize" the B table status column. Then INNER JOIN table A with an ad hoc, temporary table, here named B_agg.

    SELECT A.*, B_agg.status_agg
  FROM A
    INNER JOIN (
      SELECT a_id, ARRAY_AGG(status) status_agg FROM B GROUP BY a_id
    ) B_agg ON A.ID = B_agg.a_id
;

    Output:

    id isstatusa isstatusb isstatusc status_agg
    0 t t f {A,C}

    The temporary table query:

    SELECT a_id, ARRAY_AGG(status) status_agg FROM B GROUP BY a_id

    …outputs:

    a_id status_agg
    0 {A,C}

    This result is then is INNER JOIN‘ed with table A connecting columns A.ID and B_agg.a_id: ON A.ID = B_agg.a_id.

    The temporary table is given the alias B_agg for access outside the temporary table query, such as: B_agg.status_agg.

    Try it here: https://onecompiler.com/postgresql/3yfyffrrg

    Credit to: https://stackoverflow.com/a/6558226/2743458

    Login or Signup to reply.
  2. 0

    Using JSON_AGG() and JSON_BUILD_OBJECT() to create a JSON object and assign desired object key names:

    SELECT JSON_AGG(
  JSON_BUILD_OBJECT('A',isStatusA,'B',isStatusB,'C',isStatusB)
) status_agg
  FROM A
;

    Output:

    status_agg
    [{"A" : true, "B" : true, "C" : true}]

    Try it here: https://onecompiler.com/postgresql/3yfyjt24r

    The A table provides all the information necessary to produce the output {A: true, B: false, C:true}.

    Can also including the ID column in case multiple IDs are collected in a single query:

    SELECT JSON_AGG(
  JSON_BUILD_OBJECT(ID,
    JSON_BUILD_OBJECT('A',isStatusA,'B',isStatusB,'C',isStatusB)
  )
) status_agg
  FROM A
;

    Output:

    status_agg
    [{"0" : {"A" : true, "B" : true, "C" : true}}]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search