JSONB sqlalchemy Aggregate Function

Question

With SQLAlchemy I want to reproduce the available aggregate function jsonb_object_agg from a subquery sq_objects:

from sqlalchemy import select, func
s = select([
    sq_objects.c.object_id,
    func.jsonb_object_agg(
        sq_objects.c.keys, sq_objects.c.values).over(
        partition_by=sq_objects.c.object_id).label("attributes"),
]).\
    distinct(sq_objects.c.object_id)

However, the execute returns:

(psycopg2.ProgrammingError) can't adapt type 'method'

[ SQL: "SELECT DISTINCT ON (sq_objects.object_id)
     sq_objects.object_id,
     jsonb_object_agg(
         %(jsonb_object_agg_1)s,
         %(jsonb_object_agg_2)s
     ) OVER (PARTITION BY sq_objects.object_id) AS attributes
 FROM (SELECT ... ) AS sq_objects"
] [
parameters: {'jsonb_object_agg_1': <bound method Properties.keys of <sqlalchemy.sql.base.ImmutableColumnCollection object at 0x7f0ffb7aa828>>,
             'jsonb_object_agg_2': <bound method Properties.values of <sqlalchemy.sql.base.ImmutableColumnCollection object at 0x7f0ffb7aa828>>}]

This is the sql code I want to reproduce:

SELECT DISTINCT ON (sq_objects.object_id)
    sq_objects.object_id,
    jsonb_object_agg(
        sq_objects.keys,
        sq_objects.values
    ) OVER (PARTITION BY sq_objects.object_id) AS attributes
FROM (SELECT ... ) AS sq_objects

Answer 1

The problem is that the attributes keys and values are methods of the immutable column collection sq_objects.c. Another solution to the problem is to use item access notation for getting the actual columns:

func.jsonb_object_agg(
    sq_objects.c["keys"],
    sq_objects.c["values"]
).over(
    partition_by=sq_objects.c.object_id
).label("attributes")

Answer 2

Not the most elegant implementation, but since I now the labels of the subquery columns; a raw text input solves the issue:

func.jsonb_object_agg(
    text("sq_objects.keys"),
    text("sq_objects.values")
).over(
    partition_by=sq_objects.c.object_id).label("attributes"),