Please don't top-post, thanks. On Tue, Apr 18, 2017 at 3:18 PM, Rj Ewing <ewing...@gmail.com> wrote:
> On Tue, Apr 18, 2017 at 3:00 PM, Bruce Momjian <br...@momjian.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
