title: SQLAlchemy has_any()
body: <p>I cannot get the SQLAlchemy's <code>has_any()</code> function to work. I simply want to filter my result on a list contained in a jsonb column:</p>
  
  <pre><code>db.session.query(models.Record.id).\
      filter(models.Record.record_metadata["teams"].has_any(team_ids))
  </code></pre>
  
  <p><code>models.Record.record_metadata["teams"]</code> being a list of ids in a jsonb column (<code>record_metadata</code>) that i want to compare with <code>team_ids</code> that is also a list of ids</p>
  
  <p>The query generated is :</p>
  
  <pre><code>SELECT record.id AS record_id
  FROM record
  WHERE ((record.record_metadata -&gt; 'teams')) ?| '["id1", "id2"]';
  </code></pre>
  
  <p>That gives the error</p>
  
  <pre><code>DataError: (psycopg2.DataError) malformed array literal: "["id1", "id2"]"
  LINE 3: WHERE ((record.record_metadata -&gt; 'teams')) ?| '["id1", "id2"]'
                                                         ^
  DETAIL:  "[" must introduce explicitly-specified array dimensions.
  </code></pre>
  
  <p>But regarding <a href="https://www.postgresql.org/docs/10/static/functions-json.html" rel="nofollow noreferrer">Table 9.44. Additional jsonb Operators</a> on Postgres official documentation, the <code>|?</code> operator should be followed by an <code>array[]</code></p>
  
  <p>Indeed, when i manually write the query : </p>
  
  <pre><code>SELECT record.id AS record_id
  FROM record
  WHERE ((record.record_metadata -&gt; 'teams')) ?| array['id1', 'id2'];
  </code></pre>
  
  <p>It works just fine.</p>
  
  <p><a href="https://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSONB.Comparator.has_any" rel="nofollow noreferrer">SQLAlchemy's documentation</a> about this function is pretty poor and I can't find any examples.</p>
  
  <p>So is it me doing something wrong with this function? Should I use something else for this case? Or is it <code>has_any()</code> that is broken?</p>
  
  <p><strong>EDIT :</strong> </p>
  
  <pre><code>SELECT record.id AS record_id
  FROM record
  WHERE ((record.record_metadata -&gt; 'teams')) ?| '{"id1", "id2"}';
  </code></pre>
  
  <p>Works fine as well, and I found out that <code>has_any()</code> accepts strings as well.</p>
  
  <p>So it s a (very) nasty fix, but if I format a string with the desired ids :</p>
  
  <pre><code>db.session.query(models.Record.id).\
      filter(models.Record.record_metadata["teams"].has_any(
          str(team_ids).
          replace('[', '{').
          replace(']', '}').
          replace('\'', '\"')))
  </code></pre>
  
  <p>It works...</p>