Re: full text search on hstore or json with materialized view?

Please don't top-post, thanks.

On Tue, Apr 18, 2017 at 3:18 PM, Rj Ewing <ewing(dot)rj(at)gmail(dot)com> wrote:

> On Tue, Apr 18, 2017 at 3:00 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
>>
>> Full text search of JSON and JSONB data is coming in Postgres 10, which
>> is to to be released in September of this year:
>>
>> https://www.depesz.com/2017/04/04/waiting-for-postgresql-10-
>> full-text-search-support-for-json-and-jsonb/
>
>

A step in the right direction for me, however it doesn't appear to support
> per field full text searching.
> It is exciting though!
>

Your best bet might be to ignore the per-field searching in the initial
(indexed) pass of the query to get everything that has all the search
terms, regardless of which field they occur in. And the re-check whether
each of the found values was found in the appropriate field in a later pass.

Something like

select * from sample where
to_tsvector(json_thing->>:key1) @@ :value1
and to_tsvector(json_thing->>:key2) @@ :value2
and to_tsvector('english',json_thing) @@ (:value1 || :value2)

From the initial email:

> An idea that has come up is to use a materialized view or secondary table
with triggers, where we would have 3 columns (id, key, value).

How would this be different from the "triple store" you are abandoning?

Cheers,

Jeff