时间戳

首页 > 解决方案 > SQLAlchemy 通过 JSON 子字段加入

python - SQLAlchemy 通过 JSON 子字段加入

问题描述

我有一个现有的 SQLAlchemy 模型，它应该与另一个模型有关系，但是遗憾的是，那里的连接点是 JSON PostgreSQL 列的（子）字段。

因此，假设一个表“类别”是：

id(bigint) | name(string)
-------------------------
         5 | Comics

...第二个表“英雄”是：

id(bigint) | name(string) | info(JSON)
-------------------------------------------------------------
         2 | Tranquility  | {"category_id":5, "genre_id": 17}

我将如何在 python SQLAlchemy&PostgreSQL9.5category中hero通过hero's加入info["category_id"]以获得以下结果？

id(bigint) | name(string) | category_name | info(JSON)
-------------------------------------------------------------
         2 | Tranquility  | Comics        | {"category_id":5, "genre_id": 17}

根据这个答案，我知道在 PostgreSQL 本身中这样的事情是可能的

https://dba.stackexchange.com/a/83935 ( http://sqlfiddle.com/#!15/226c33/1 )

并且可能需要一些技巧来转换或绕过默认方式
，如sqlalchemy filter by json 字段中所述

注意：请注意，我无法更改数据库结构或模型的现有部分，我也不是在寻找我已经知道的双重查询绕过。

标签： pythonjsonpostgresqljoinsqlalchemy

解决方案

您可以使用json_to_record创建内存表，然后加入该表。

https://www.postgresql.org/docs/9.4/functions-json.html

已经回答了这个问题的另一个问题，但json_to_recordset作为数据使用的是对象列表 https://stackoverflow.com/a/31333794/3358570

这是您可以根据需要转换为 sqlalchemy 的 SQL，

SELECT hero.id as hero_id, hero.name as name, d.name as category_name, info
FROM hero,
     json_to_record(hero.info) AS x(category_id int)
         JOIN category d on d.id = category_id

这里为您提供 db fiddle https://www.db-fiddle.com/f/7ops4KXVDF6KpY5JZau7vG/1

另一种更简单的方法是在 join 和 type caste 中访问 JSON 值category_id

SELECT hero.id       AS hero_id,
       hero.name     AS hero_name,
       category.name AS category_name,
       category.id   AS category_id,
       hero.info     AS hero_info
FROM hero
     JOIN category ON category.id = CAST(hero.info ->> 'category_id' AS INTEGER)

等效的 qalchemy 会变成，

category_id = cast(Hero.info.op("->>")("category_id"), Integer)
query = (db.session.query(Hero.id, Hero.name, Category.name, Category.id, Hero.info)
         .join(Category, Category.id == category_id))
data = query.all()

推荐阅读