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.
CodePudding user response:
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
CodePudding user response:
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}}] |