Re: [GENERAL] full text search on hstore or json with materialized view?
| От | Jeff Janes |
|---|---|
| Тема | Re: [GENERAL] full text search on hstore or json with materialized view? |
| Дата | |
| Msg-id | CAMkU=1yeOY4prqugphAK6-2zrk-CHvFnUeQfL=UcQRKLX+EXcw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: [GENERAL] full text search on hstore or json with materialized view? (Rj Ewing <ewing.rj@gmail.com>) |
| Ответы |
Re: [GENERAL] full text search on hstore or json with materialized view?
|
| Список | pgsql-general |
Please don't top-post, thanks.
On Tue, Apr 18, 2017 at 3:18 PM, Rj Ewing <ewing.rj@gmail.com> wrote:
On Tue, Apr 18, 2017 at 3:00 PM, Bruce Momjian <bruce@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
В списке pgsql-general по дате отправления: