'SQLAlchemy: multiple distinct on aggregation
I want to run the following query against a PostgreSQL database:
SELECT table.colA AS colA, array_agg(DISTINCT (table.colB, table.colC)) AS combinations
FROM table
WHERE table.time BETWEEN '...' AND '...'
GROUP BY table.colA;
The problem lies in the aggregation on distinct values of more than one column. I tried the following sqlalchemy statements:
# ... imports, engine declaration, metadata extraction
from sqlalchemy.dialects.postgresql import array_agg
table = Table("table", metadata_obj, autoload_with=engine)
stmt_text = (
text(f"""
SELECT table.colA AS colA,
array_agg(DISTINCT (table.colB, table.colC)) AS combinations
FROM table
WHERE table.time BETWEEN '{time_start}'AND '{time_end}'
GROUP BY table.colA;
"""
)
)
stmt_part = (
select(
table.c.colA.label('colA'),
array_agg(
distinct(text('`table.colB`, `table.colC`'))
).label('combinations')
)
.where(table.c.time.between(time_start, time_end))
.group_by(table.c.colA)
)
stmt_full = (
select(
table.c.colA.label('colA'),
array_agg(
distinct(tuple_(table.c.colB, table.c.colC))
).label('combinations')
)
.where(table.c.time.between(time_start, time_end))
.group_by(table.c.colA)
)
stmt_textreturns the correct data, but not in a JSON format:
(UUID('abcdefghi'), '{"(YMPRRQED_ANEXXY,RANDGR_QAPODIF)","DIGNEIDNF_SJNDJKDFSJD,DKDHFH_DNFJD)"}')
stmt_part(see https://stackoverflow.com/a/61465391/5599687) raises an error
SyntaxError: syntax error at or near ","
LINE 1: ..._id, array_agg(DISTINCT `table.colB`, `tabl...
^
stmt_part_2returns a wrong result:
('abcdefghi', ['{', '"', '(', 'Y', 'M', 'P', 'R', 'R', 'Q', 'E', 'D', '_', 'A', 'N', 'E', 'X', 'X', 'Y', ',', 'R', 'A', 'N', 'D', 'G', 'R', '_', 'Q', 'A', 'P', 'O', ... (65 characters truncated) ... 'A', 'X', 'E', 'D', '_', 'A', 'A', 'E', 'A', 'G', 'A', ',', 'A', 'A', 'A', 'A', 'G', 'A', '_', 'A', 'A', 'P', 'B', 'R', 'Q', 'E', 'Q', ')', '"', '}'])
-stmt_full raises an error:
NotImplementedError: The tuple type does not support being fetched as a column in a result row.
How can I obtain the results as with stmt_text, but with an ORM style similar to the other stmt_..., and already with a JSON format?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
