如何在SQLAlchemy中获取PostgreSQL jsonb_each返回record的key字段？

我有一个返回record的查询，需要访问该record的某个字段。在PostgreSQL中可通过以下语句实现（items.properties为jsonb列）：

SELECT (p).key FROM items, jsonb_each(items.properties) p;

我目前编写了如下SQLAlchemy代码：

properties_data = func.jsonb_each(ItemModel.properties).alias("p")
q = db.query(properties_data).select_from(ItemModel)

这段代码生成的SQL如下，但我无法找到访问p的key字段的方法：

SELECT p.jsonb_each_1 AS p_jsonb_each_1 
FROM stac.items, jsonb_each(stac.items.properties) AS p

注：我不想在Python中返回值后再访问key，而是要将此查询作为子查询用于另一个查询。

解决方案 #

要直接在SQL层面访问jsonb_each返回的record的key字段，你可以通过以下方式实现：

方法1：通过别名对象的.c.key访问

当你用alias("p")给jsonb_each的结果起别名后，可以直接通过别名的.c.key来引用key字段，这是SQLAlchemy处理复合类型返回值的标准方式：

properties_data = func.jsonb_each(ItemModel.properties).alias("p")
q = db.query(properties_data.c.key).select_from(ItemModel)

这段代码会生成符合预期的SQL：

SELECT p.key 
FROM stac.items, jsonb_each(stac.items.properties) AS p

方法2：使用tuple_结合索引访问

如果第一种方法出现兼容问题，也可以用tuple_包裹别名后的结果，通过索引获取key（jsonb_each返回的record中，key是第一个元素，value是第二个）：

from sqlalchemy import tuple_

properties_data = func.jsonb_each(ItemModel.properties).alias("p")
q = db.query(tuple_(properties_data).get(0)).select_from(ItemModel)

这会生成和原生SQLSELECT (p).key等价的查询语句。

作为子查询使用

如果要将这个查询作为子查询嵌入其他查询，只需将其转为子查询后引用字段即可：

properties_data = func.jsonb_each(ItemModel.properties).alias("p")
subq = db.query(properties_data.c.key.label("prop_key")).select_from(ItemModel).subquery()

# 示例：在主查询中关联子查询
main_query = db.query(ItemModel.id).join(subq, ...)

内容的提问来源于stack exchange，提问作者kaveh