...

или внеочередные заметки


Previous Entry Add to Memories Share Next Entry
Jsonb: "Wildcard" query
trekking, Himalaya
obartunov
Just to let people know about our experiments.


Consider this top-level query, which well supported by GIN in 9.4.

postgres=# select count(*) from jb where jb @> '{"tags":[{"term":"NYC"}]}'::jsonb;
count
-------
285
(1 row)

What if I want to find just {"term":"NYC"}, or even "NYC" ? Now, with some improvements, jsonb_hash_ops supports such queries with less than 5% bigger index size and not sacrificing performance !

postgres=# select count(*) from jb where jb @>> '{"term":"NYC"}'::jsonb;
count
-------
285
(1 row)
postgres=# select count(*) from jb where jb @>> '"NYC"'::jsonb;
count
-------
285
(1 row)


I can see the use-case for wildcard queries for shop aggregators, which combine different hierachies, so it's difficult to say if some specific key is on the same level in different sources.

Tags: , ,

combining tsearch's prefix-search

Andreas Joseph Krogh

2014-07-07 09:36 am (UTC)

Hi Oleg!
I'm very excited about your work, nice!

I posted this on pgsql-general, don't know if you follow that list, so I'll ask here.

Is it possible to combine tsearch's prefix-search with the new JSONB-format?

Something like this (pseudo-code):
SELECT '{"subject": "visena"}'::jsonb @> '{"subject": to_tsquery('simple', '(vise:*|office:*)')}';

and have the above query match documents where subject contains the prefixes "vise" and "office", which would match subject="visena" and subject="officenet".
expanding this to be able to search for things like:

subject=to_tsquery('simple', '(vise:*|office:*)') OR recipient=to_tsquery('simple', '(somefirstname:*|somelastname:*)') OR content=to_tsquery('simple', '(string1:*&string2:*)')

Would be ultimate cool if this was possible!

Thanks.

--
Andreas

You are viewing obartunov