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. — Ilja Everilä
– Ilja Everilä, Commented Sep 20, 2017 at 9: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
– HippoMan, Commented Sep 20, 2017 at 12:58

James Mishra · Accepted Answer · 2021-04-08 08:58:48Z

2

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.

answered Apr 8, 2021 at 8:58

James Mishra

4,5805 gold badges37 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HippoMan · Accepted Answer · 2017-09-19 14:26:34Z

1

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

answered Sep 19, 2017 at 14:26

HippoMan

2,3502 gold badges26 silver badges56 bronze badges

Collectives™ on Stack Overflow

sqlalchemy/postgres: arithmetic on JSON fields?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related