title: SQLalchemy Json-configured varchar: how to filter
body: <p>I am using MySQL 8.0 and SQLalchemy.</p>
  <p>I have a model as follows:</p>
  <pre><code>class Json(TypeDecorator):
      impl = String(1000)
  
      def process_bind_param(self, value, dialect):
          return json.dumps(value)
  
      def process_result_value(self, value, dialect):
          if not value:
              return None
          return json.loads(value)
  
  class BookCommerce(Base):
      bcDetails = Column(Json)
  </code></pre>
  <p>This is rendered as varchar(1000) in the database, and the decorator means it all works well for accessing dict values that I save or retrieve from that field.</p>
  <p>My problem is that I now want to search the field looking for values (including value fragments expressed as <code>%my_string%</code>). A query like this yields nothing:</p>
  <pre><code>BookCommerce.query.filter(BookCommerce.bcDetails.like(fragname))).all()
  </code></pre>
  <p>since SQLA (I think) renders the varchar into a JSON document before executing the query.
  Strangely, the SQL resulting from this query does actually work in a MySQL command window.</p>
  <p>I've tried the accepted solution in <a href="https://stackoverflow.com/questions/39470239/sqlalchemy-json-column-how-to-preform-a-contains-query#39470478">this</a> question, but it refers to the JSON type in MySQL and therefore croaks on the syntax (in Python and MySQL command line).</p>
  <p>Any suggestions for how to handle this properly? I don't want to move away from this Json-decorated solution in general, I simply want a free-form search to locate matching records.</p>
  <p>SQL queries obtained from echo=True on create_engine:</p>
  <pre><code>2021-03-25 19:47:11,638 INFO sqlalchemy.engine.Engine SELECT bookcommerce.`bcDetails` AS `bookcommerce_bcDetails` 
  FROM bookcommerce 
  WHERE bookcommerce.`bcDetails` LIKE %(bcDetails_1)s
  2021-03-25 19:47:11,656 INFO sqlalchemy.engine.Engine [cached since 46.19s ago] {'bcDetails_1': '&quot;%test%&quot;'}
  2021-03-25 19:47:11,663 DEBUG sqlalchemy.engine.Engine Col ('bookcommerce_bcDetails')
  2021-03-25 19:47:11,667 INFO sqlalchemy.engine.Engine ROLLBACK
  </code></pre>