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

Question

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 got 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.

Expected output: {A: true, B: false, C:true}

@Pankaj I want {A: true, B: false, C:true} or something similar with using pivot — Sebastian Halik
– Sebastian Halik, Commented Sep 14, 2022 at 6:27

bloodyKnuckles · Accepted Answer · 2022-09-14 06:30:43Z

1

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

edited Sep 14, 2022 at 6:30

answered Sep 13, 2022 at 23:52

bloodyKnuckles

12.2k4 gold badges32 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sebastian Halik Over a year ago

Thank you. That's something similar that i expected, but not the same. I getting {A,B,C} as result from "B_agg.status_agg". I wanted {A: true, B: false, C:true} or something similar with using pivot

Sebastian Halik Over a year ago

Ok. I fixed it on my own. For future devs with same problem : I select now CASE WHEN A.isStatusA = true and 'A' = ANY (B_agg.status_agg) THEN true else false. Same with B but with changed letter. Thank you for help!

bloodyKnuckles · Accepted Answer · 2022-09-14 07:34:56Z

1

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

edited Sep 14, 2022 at 7:34

answered Sep 14, 2022 at 7:16

bloodyKnuckles

12.2k4 gold badges32 silver badges39 bronze badges

1 Comment

Sebastian Halik Over a year ago

I have to check in both tables. Your comment was my first version and later i got information that i have to check also in table B. problem already solved i think :)

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related