JSONB sqlalchemy聚合函数 [英] JSONB sqlalchemy Aggregate Function

问题描述

我想使用SQLAlchemy复制可用的集合函数子查询 sq_objects 中的jsonb_object_agg：

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)

但是，执行返回：

（psycopg2.ProgrammingError）可以' t适应类型'方法'

(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>>}]

这是我要复制的SQL代码：

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

推荐答案

问题是属性键和 values 是不可变列的方法集合 sq_objects.c 。解决该问题的另一种方法是使用项目访问符号获取实际的列：

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

这篇关于JSONB sqlalchemy聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！