sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does not exist: jsonb = uuid",

[Jesse Finnegan]

Bare with me. I'm some what new to SQL Alchemy so feel free to let me know if there is any info I can provide.

I was wondering how I could type cast a jsonb (which should have a UUID init) to a UUID or vice versa in a .join?

I have the following code:

 .join(
    CandidateActivity,
    db.and_(
        User.id == CandidateActivity.candidate_id,
        CandidateActivity.type.in_(['question_answered', 'question_viewed', 'feedback_clicked', 'feedback_inbound']),
        db.or_(CandidateActivity.data['track']['id'] == UserTrack.track_id, CandidateActivity.data['track']['track_id'] == UserTrack.track_id)
        ),
    isouter=True
 ) \

with the db.or_ causing the following error:

"sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator does not exist: jsonb = uuid",
        "LINE 2: ...(((candidate_activities.data -> 'track') -> 'id') = user_tra...",
        "                                                             ^",
        "HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.",

I've tried casting one or the other to a string with no luck and I don't see anything in google that specifies the solution to this problem that I have found.

Thank you in advance for any help.

[Mike Bayer]

you probably want to use cast()

from sqlalchemy.dialects.postgresql import UUID

from sqlalchemy import cast

cast(MyModel.data['track']['id'], UUID) == UserTrack.id

see the examples at https://docs.sqlalchemy.org/en/13/core/type_basics.html#sqlalchemy.types.JSON

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/e273b6d6-b851-4914-aa02-9e50ac3b7105%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.