Support json in query

[tiyujopite (José Antonio Díaz Miralles)]

Hi, I would like to know if python-sql has support for this type of queries.
And if yes, how is the syntax?

Thanks in advance.

SELECT id
FROM table
WHERE data::json ->> 'key' = 'my_key'

[ced (Cédric Krier)]

Yes but you have to create your own JSON operator because it is not SQL standard like this:

class JSONExtract(BinaryOperator):
    __slots__ = ()
   _operator = '->>'

[tiyujopite (José Antonio Díaz Miralles)]

It worked perfectly, thank you very much.
I leave a code fragment in case it is useful to someone else.

from sql.operators import BinaryOperator
from sql import Cast, Table


class JSONExtract(BinaryOperator):
    __slots__ = ()
    _operator = '->>'


table = Table('table_table')
query = table.select(
    table.id,
    where=(
        (table.my_column == 'foo')
        & (JSONExtract(Cast(table.data, 'json'), 'key') == 'bar')
        ))
cursor.execute(*query)
print(cursor.fetchall())