'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_text returns the correct data, but not in a JSON format:
(UUID('abcdefghi'), '{"(YMPRRQED_ANEXXY,RANDGR_QAPODIF)","DIGNEIDNF_SJNDJKDFSJD,DKDHFH_DNFJD)"}')

SyntaxError: syntax error at or near ","
LINE 1: ..._id, array_agg(DISTINCT `table.colB`, `tabl...
                                               ^
  • stmt_part_2 returns 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