postgresql - 来自 jsonb 的 SQLAlchemy Pandas read_sql

Question

我想用 pandas 生成一个数据框 read_sql来 self 的带有 PostgreSQL 的 jsonb 的 sqlalchemy 查询属性列。

实际上这会给我答案:

query = session.query(
    cls.id,
    cls._my_jsonb_column
).all()
pd.DataFrame.from_dict([dict(id=id_, **i) for id_,i in query])

但我更愿意使用 PostgreSQL 而不是在应用程序中解压 jsonb。

我的尝试给出了

query = session.query(
    cls.id,
    func.jsonb_to_record(cls._my_jsonb_column)
)
pd.read_sql(query.statement, query.session.bind)

(psycopg2.NotSupportedError) function returning record called in context that cannot accept type record

Answer 1

json_to_record(和 jsonb_to_recordset)返回记录，就像它是 SELECT 查询的结果一样。在 sqlalchemy 上下文中，它提供了一个可以像表一样使用的可选对象。

因此，您应该将 func.jsonb_to_record(cls._my_jsonb_column) 的结果视为一种可以加入到您的原始表中的表。

你的查询应该是这样的:

jsonb_data = func.jsonb_to_record(cls._my_jsonb_column)
query = session.query(
    select(
        [cls.id, <other columns>]
    ).select_from(
        cls.join(jsonb_data, <on_clause>)
    )
)

您甚至可以使用 JSON processing functions 展平您的 JSON 数据但如果不知道 JSON 数据的结构，就不可能更精确。

或者，我最近发布了一个包，可以根据 json 数据的描述轻松地展平 JSONB 字段，我很乐意收到一些反馈:pg_jsonb_flattener