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

Ilja Everilä · Accepted Answer · 2019-03-14 05:52:36Z

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")

answered Mar 14, 2019 at 5:52

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joost Döbken · Accepted Answer · 2017-05-05 11:57:53Z

1

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"),

answered May 5, 2017 at 11:57

Joost Döbken

4,1576 gold badges50 silver badges96 bronze badges

Collectives™ on Stack Overflow

JSONB sqlalchemy Aggregate Function

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related