sqlalchemy/postgres: arithmetic on JSON fields?

Question

I am using sqlalchemy on a postgres database, and I'm trying to do arithmetic in a SELECT on two JSON fields which represent floats. However, I have not figured out how to make this work.

Assume I have properly defined a table called transactions which contains a JSON column called cost_data, and assume that this JSON structure contains two attributes called cost and subtotal which represent float values.

In a SELECT statement, I generate the sum of those two fields as follows:

(cast(transactions.c.cost_data['subtotal'], sqlalchemy.Float) + cast(transactions.c.cost_data['cost'], sqlalchemy.Float)).label('total_cost')

This generates the following SQL fragment ...

CAST((transactions.cost_data -> %(cost_data_6)s) AS FLOAT) + CAST((transactions.cost_data -> %(cost_data_7)s) AS FLOAT) AS total_cost

(where cost_data_6 and cost_data_7 get set to subtotal and cost, respectively).

However, I get the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) cannot cast type json to double precision

If I remove the casting and do it as follows, it also fails ...

(transactions.c.cost_data['subtotal'] + transactions.c.cost_data['cost']).label('total_cost')

I get this error ...

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does not exist: json || json
LINE 9: ... (transactions.cost_data -> 'subtotal') || (transa...
                                                   ^

Clearly, this is because the fields come in as strings, and the "+" operator gets interpreted as string concatenation.

Also, if I use the Python float operator, it also fails ...

(float(transactions.c.cost_data['subtotal']) +  float(transactions.c.cost_data['cost'])).label('total_cost')

The python interpreter doesn't even execute the code, and it gives this error:

TypeError: float() argument must be a string or a number, not 'BinaryExpression'

So how can I perform the addition of those two fields using sqlalchemy?

PS: the following is a typical cost_data column value ...

{"cost":3.99,"subtotal":12.34}

Note that money and floating point might be a bad match. [You could use numeric instead](https://stackoverflow.com/questions/45689496/query-a-specific-json-column-postgres-with-sqlalchemy). — Ilja Everilä, Sep 20 '17 at 09:56
Agreed, in general. In this case, I am not able to change the database column definitions, and other software uses floats for these fields. — HippoMan, Sep 20 '17 at 12:58

score 1 · Accepted Answer · answered Sep 19 '17 at 14:26

OK. I finally figured it out. I have to pass each reference through the astext operator before applying cast, as follows ...

(transactions.c.cost_data['subtotal'].astext.cast(sqlalchemy.Float) + transactions.c.cost_data['cost'].astext.cast(sqlalchemy.Float)).label('total_cost')

score 1 · Answer 2 · answered Apr 08 '21 at 08:58

The accepted answer works when only using SQLAlchemy with PostgreSQL, but it is not portable to other databases that have JSON types.

If you want to write code that is portable between PostgreSQL, MySQL, and SQLite, then you should cast a JSON column using one of the column's as_*() methods.

OP's answer would look like:

transactions.c.cost_data['subtotal'].as_float()

The methods .as_float(), .as_string(), .as_boolean(), and .as_integer() are available on SQLAlchemy JSON columns since SQLAlchemy version 1.3.11. More documentation here.

sqlalchemy/postgres: arithmetic on JSON fields?

2 Answers2