title: PostgreSQL: pg_trgm full-text search with jsonb columns?
body: <p>I have a table with a jsonb column where I store variable data. I would like to search this column and also find fragments (leading or trailing whitespace). I think I know how to do this with text columns but cannot wrap my head around how to achieve this with <code>jsonb</code> columns.</p>
  
  <p>There are two scenarios that I would like to achieve:</p>
  
  <ol>
  <li>Search a specific key inside the jsonb column only (for example
  <code>data-&gt;&gt;company</code>)</li>
  <li>Search the whole <code>jsonb</code> column</li>
  </ol>
  
  <p>For text columns I generate <a href="https://stackoverflow.com/questions/28975517/difference-between-a-gist-index-and-gin-index/28976555#28976555">gin</a> indexes using <a href="https://www.postgresql.org/docs/current/static/pgtrgm.html" rel="noreferrer">pg_trgm</a>. </p>
  
  <p>Install extension pg_trgm:</p>
  
  <pre><code>CREATE extension if not exists pg_trgm;
  </code></pre>
  
  <p>Create table &amp; index:</p>
  
  <pre><code>CREATE TABLE tbl (
      col_text  text,
      col_json  jsonb
  );
  
  CREATE INDEX table_col_trgm_idx ON tbl USING gin (col_text gin_trgm_ops);
  </code></pre>
  
  <p>Example query:</p>
  
  <pre><code>SELECT * FROM tbl WHERE col_text LIKE '%foo%';   -- leading wildcard
  SELECT * FROM tbl WHERE col_text ILIKE '%foo%';  -- works case insensitive as well
  </code></pre>
  
  <p>Trying the same with the jsonb column fails. If I try to index the whole column</p>
  
  <pre><code>CREATE INDEX table_col_trgm_idx ON tbl USING gin (col_json gin_trgm_ops);
  </code></pre>
  
  <p>I get the error </p>
  
  <pre><code>ERROR (datatype_mismatch): operator class "gin_trgm_ops" does not accept data type jsonb
  </code></pre>
  
  <p>(Which makes sense). If I try to index just one key of the jsonb column I also receive an error:</p>
  
  <pre><code>CREATE INDEX table_col_trgm_idx ON tbl USING gin (col_json-&gt;&gt;company gin_trgm_ops);
  </code></pre>
  
  <p>Error:</p>
  
  <pre><code>ERROR (syntax_error): syntax error at or near "-&gt;&gt;"
  </code></pre>
  
  <p>I used <a href="https://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations/13452528#13452528">this answer</a> by <a href="https://stackoverflow.com/users/939860/erwin-brandstetter">@erwin-brandstetter</a> as a reference. Any help is highly appreciated (and no, I don't want to implement Elasticsearch as of now :) ).</p>
  
  <hr>
  
  <p><strong>Edit:</strong> Creating the index like this actually works:</p>
  
  <pre><code>CREATE INDEX table_col_trgm_idx ON tbl USING gin ((col_json-&gt;&gt;'company') gin_trgm_ops);
  </code></pre>
  
  <p>And querying it also doesn't lead to an error:</p>
  
  <pre><code>SELECT * FROM tbl WHERE col_json-&gt;&gt;'company' LIKE '%foo%'; 
  </code></pre>
  
  <p>But the result is always <strong>empty</strong>.</p>