Is it possible to aggregate table rows to a single list within a sql query in SQLalchemy?
Guess we have Table1, Table2, Table3. We have “id” shared between tables. I know that “id” is the primary key for Table1 and Table2, but a foreign key in Table3. I would like to have a statement like this:
(select(Table1, Table2, <Aggregate Table3 to list> )
.join(Table2, onclause=Table2.id == Table1.id, full=True)
.join(Table3, onclause=Table3.id == Table1.id, full=True)
.group_by(Table1.id)
.where(Table1.id==<id-of-interest>))
The result should be a single row containing the columns of Table1, Table2 and a single column with the aggregated list. Any help is appreciated. In the example below, id_1 is the id of interest.
Table1:
| Id | table1_col |
|---|---|
| id_1 | table1_col_cont |
Table2:
| Id | table2_col |
|---|---|
| id_1 | table2_col_cont |
Table3:
| table3_id | Id | table3_col_a | table3_col_b |
|---|---|---|---|
| 1 | id_1 | table3_col_a_cont1 | table3_col_b_cont1 |
| 2 | id_1 | table3_col_a_cont2 | table3_col_b_cont2 |
| 3 | id_2 | table3_col_a_cont3 | table3_col_b_cont3 |
Result of select:
| Id | table1_col | table2_col | table3_col_agg |
|---|---|---|---|
| id_1 | table1_col_cont | table2_col_cont | [{“table3_col_a”:”table_col_a_cont1″,”table3_col_b”:”table3_col_b_cont1″},{“table3_col_a”:”table_col_a_cont2″,”table3_col_b”:”table3_col_b_cont2″}] |
6