Postgresql search hstore values

Postgresql search hstore values

      -- 0. Create a bunch of id => vector results for every definition
      WITH unnested_keys_as_vector AS (
        SELECT id as term_id, to_tsvector((each(definitions)).value) as term
        FROM glossary_terms
        WHERE glossary_id = 10
      )
      -- 2. Select the rank so we can sort by it (default order is order of terms in table)
      SELECT *, ts_rank_cd(term, to_tsquery('I | like | bananas')) as rank
      FROM glossary_terms
      -- 1. Join the result of the tsvector search on the terms
      JOIN (SELECT term_id, term FROM unnested_keys_as_vector
            WHERE term @@ to_tsquery('I | like | bananas') ) uks ON glossary_terms.id = term_id
      WHERE glossary_id = #{id}
      -- 3. Order by rank so we get best matching first
      ORDER BY rank DESC