sum array in json

[AfroMonkey]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from sqlalchemy.dialects.postgresql import JSONB
from sqlmodel import (
    Column,
    Field,
    Numeric,
    Session,
    SQLModel,
    create_engine,
    func,
    select,
)


class Hero(SQLModel, table=True):
    id: int = Field(primary_key=True)
    json_column: dict = Field(sa_column=Column(JSONB))


def test_jsonb():
    engine = create_engine(
        "postgresql+psycopg2://postgres:postgres@localhost:5432/hero", echo=True
    )

    SQLModel.metadata.create_all(engine)

    session = Session(engine)

    session.add(
        Hero(json_column={"matches": [{"score": 1}, {"score": 2}, {"score": 3}]})
    )
    session.add(
        Hero(json_column={"matches": [{"score": 4}, {"score": 5}, {"score": 6}]})
    )

    res = session.exec(select(Hero)).all()
    python_sum = sum(
        match["score"] for hero in res for match in hero.json_column["matches"]
    )
    assert python_sum == 21

    matches_elements = func.jsonb_array_elements(
        Hero.json_column["matches"]
    ).table_valued(
        "value"
    )  # if literal `value` changed, in line matches_elements.c.SOMETHING cannot access it, no matter the name

    sql_sum = session.exec(
        select(
            func.sum(matches_elements.c.value.op("->>")("score").cast(Numeric))
        ).select_from(Hero, matches_elements)
    ).one()
    assert sql_sum == python_sum

Description

Is there a better way to access to the score data? The c.value.op("->>")("score") seems suboptimal. Also a kind of "fragile", if in table_valued is used something else than value, it stops working

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.24

Python Version

Python 3.13.2

Additional Context

No response