title: sqlalchemy filter by json field
body: <p>I have model with <code>json column</code>. Example of model and data:</p>
  
  <pre><code>app = Flask(__name__)
  app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://...'
  
  db = SQLAlchemy()
  db.init_app(app)
  app.app_context().push()
  
  class Example(db.Model):
      id = db.Column(db.Integer(), nullable=False, primary_key=True, )
      json_field = db.Column(db.JSON())
  
  db.create_all()
  db.session.add(Example(json_field={'id': None}))
  db.session.add(Example(json_field={'id': 1}))
  db.session.add(Example(json_field={'id': 50}))
  db.session.add(Example(json_field={}))
  db.session.commit()
  </code></pre>
  
  <p>Now I try to find records where <code>id == 1</code>:</p>
  
  <pre><code>query = db.session.query(Example).filter(Example.json_field['id'] == 1)
  print(query.all())
  </code></pre>
  
  <p>And I getting the next error:</p>
  
  <blockquote>
    <p>sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator
    does not exist: json = integer LINE 3: WHERE (example.json_field ->
    'id') = 1</p>
  </blockquote>
  
  <p><strong>The reason.</strong> Look at generated query:</p>
  
  <pre><code>SELECT example.id AS example_id, example.json_field AS example_json_field 
  FROM example 
  WHERE (example.json_field -&gt; %(json_field_1)s) = %(param_1)s
  </code></pre>
  
  <p>But in my case correct query should be like this:</p>
  
  <pre><code>SELECT * FROM example WHERE CAST(json_field-&gt;&gt;'id' AS INTEGER) = 1;
  </code></pre>
  
  <p><strong>How can I do this?</strong></p>
  
  <p>I have tried use <a href="https://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.cast" rel="noreferrer">cast</a>, but unsuccessfully:</p>
  
  <pre><code>print(
      db.session.query(Example).filter(
          cast(Example.json_field['id'], Integer) == 1
      ).all()
  )
  </code></pre>
  
  <p>The error:</p>
  
  <blockquote>
    <p>sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) cannot
    cast type json to integer LINE 3: WHERE CAST((example.json_field ->
    'id') AS INTEGER) = 1</p>
  </blockquote>
  
  <p>As you can see <code>where clause</code> still wrong. Also I need to use range (<code>&gt;</code>, <code>&lt;=</code> etc.) conditions. Thanks for help.</p>