title: Postgres seach jsonb with indexes
body: <p>Im new to postgres jsonb operation.
  Im storing some data in Postgres with jsonb column, which has flexible metadata as below.
  I wanted to search different unique metadata (key:value pairs)</p>
  <pre><code>id, type, metadata
  1, player, {&quot;name&quot;: &quot;john&quot;, &quot;height&quot;: 180, &quot;team&quot;: &quot;xyz&quot;}
  2, game,   {&quot;name&quot;: &quot;afl&quot;, &quot;members&quot;: 10, &quot;team&quot;: &quot;xyz&quot;}
  </code></pre>
  <p>results should be something like below, distinct, order by asc. I wanted it to be efficient using some indexes.</p>
  <pre><code>key    | value
  ______________
  height   180
  members  10
  name     alf
  name     john
  team     xyz
  
  </code></pre>
  <p>My solution below hit the index for search but sorting and distinct wont hit any indexes as they are processed values from jsonb.</p>
  <pre><code>CREATE INDEX metadata_jsonb_each_text_idx ON table
  USING GIN (jsonb_pretty(metadata) gin_trgm_ops);
  
  select distinct t, t.* 
  from table u, jsonb_each_text(u.metadata) t 
  where jsonb_pretty(u.metadata) like '%key%' 
  order by t.key, t.value
  </code></pre>
  <p>Appreciate any thoughts on this issue.
  Thanks!</p>