SQLAlchemy join through JSON sub field

Question

I have an existing SQLAlchemy model that is supposed to have a relation to another model, however the connecting point there is sadly a (sub)field of a JSON PostgreSQL column.

So let's say one table "category" is:

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

... and second table "hero" is:

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

How would I, in python SQLAlchemy&PostgreSQL9.5, join category on the hero through the hero's info["category_id"] to get the following results?

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

I know such things are possible in the PostgreSQL itself according to this answer

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

And that there probably needs to be some trickstery of casting or bypassing default ways
as described in sqlalchemy filter by json field

Note: Please note that I can't change the DB structure nor the existing parts of the model and I am also not looking for a double query bypass which I already know.

Answer 1

You can use json_to_record to create an in-memory table and then join on that.

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

Another question which already answered this, but that was using json_to_recordset as the data was a list of objects https://stackoverflow.com/a/31333794/3358570

Here is the SQL you can transform to sqlalchemy according to your need,

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

Here db fiddle for you https://www.db-fiddle.com/f/7ops4KXVDF6KpY5JZau7vG/1

Another, a simpler approach would be to access JSON value in join and type caste 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)

equivalent sqalchemy would become,

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()