SQLAlchemy查询：将JSON对象的键提取为数组返回，而不是分成多行

后端开发 2026-07-11

假设我有一个看起来像下面这样的SQLAlchemy模型：

class MyModel(meta.Base)
    id = Column(BigInteger, autoincrement=True, primary_key=True)
    extra_data = Column(postgresql.JSON, nullable=False)

下面是我的应用代码：

import model
from sqlalchemy import func


model.MyModel(extra_data={"key1": "value1", "key2": "value2"})
model.MyModel(extra_data={"key3": "value3", "key4": "value4"})

results = meta.session.query(
    model.MyModel.id,
    func.jsonb_object_keys(model.MyModel.extra_data).label('keys')
).all()

print(f"Result rows:")
for row in results:
    print(f"Row ID: {row.id}, Keys: {row.keys}")

这会产生如下输出：

Result rows:
Row ID: 1, Keys: <bound method Row.keys of (1, 'key1')>
Row ID: 1, Keys: <bound method Row.keys of (1, 'key2')>
Row ID: 2, Keys: <bound method Row.keys of (2, 'key3')>
Row ID: 2, Keys: <bound method Row.keys of (2, 'key4')>

然而我期望/需要看到这样的输出：

Result rows:
Row ID: 1, Keys: ['key1' 'key2']
Row ID: 2, Keys: ['key3' 'key4']

我该如何调整查询，将每个myModel行中的所有JSON键合并成一个列表？

更新：

我已经尝试像下面这样使用 func.array_agg() 搭配 .group_by()：

    query = meta.session.query(
        model.MyModel.id,
        func.array_agg(func.jsonb_object_keys(model.MyModel.extra_data))
    ).group_by(model.MyModel.id)

但这给了我这个错误：

sqlalchemy.exc.NotSupportedError: (psycopg2.errors.FeatureNotSupported) aggregate function calls cannot contain set-returning function calls
                                                         ^
HINT:  You might be able to move the set-returning function into a LATERAL FROM item.

更新2：

我这样确定了我的SQLAlchemy版本：

from sqlalchemy import __version__
print(f"{__version__=}")

这报告了 __version__='1.4.52'

解决方案

你可以使用一个 jsonpath 表达式，通过 keyvalue().key 提取 jsonb_path_query_array()。将 SQLAlchemy 2.0.48示例调整为你的版本：

results = meta.session.query(
    model.MyModel.id,
    func.jsonb_path_query_array(model.MyModel.extra_data, cast("$.keyvalue().key", JSONPATH))
).all()

我认为在 1.42.x 版本中，你可以尝试使用一个 literal_column：

results = meta.session.query(
    model.MyModel.id,
    func.jsonb_path_query_array(model.MyModel.extra_data, literal_column("'$.keyvalue().key'::jsonpath"))
).all()

db<>fiddle的演示

select id,jsonb_path_query_array(extra_data,'$.keyvalue().key')
from mymodel;

id	jsonb_path_query_array
1	["key1", "key2"]
2	["key3", "key4"]

这两个 array_agg() 和 group by 也应该可用：

select id,array_agg(k)
from mymodel
cross join jsonb_object_keys(extra_data)as ok(k)
group by id
order by id;

id	array_agg
1	{key1,key2}
2	{key3,key4}

在SQLAlchemy中，使用一个 subquery 可能比尝试用一个返回集合的函数来表达join更容易。就你的情况，我想大致是这样的

subq = (select(model.MyModel.id,
               func.jsonb_object_keys(model.MyModel.extra_data).label('keys')
              ).subquery())

select(subq.c.id, func.array_agg(subq.c.keys))
.group_by(subq.c.id)

这应该生成一条与如下所示相似的SQL：

SELECT anon_1.id, array_agg(anon_1.keys) AS array_agg_1 
FROM (SELECT mymodel.id AS id, jsonb_object_keys(mymodel.extra_data) AS keys
FROM mymodel) AS anon_1 GROUP BY anon_1.id

一个区别是，从 [jsonb_object_keys()] 聚合 key 列会得到一个 text[] 数组，而 [jsonb_path_query_array()] 列则会产生一个包含字符串的JSON数组的 jsonb。我不确定这在多大程度上会影响你在SQLAlchemy中如何取值，但在原始SQL级别这是一个有意义的差异。

JSON