JSONB sqlalchemy聚合函数

本文介绍了JSONB sqlalchemy聚合函数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想使用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")