| Hi In my quest of JSONB querying and searching without having to actually cast into a text, I found JSQuery I do admit my JSONB knowledge shortcoming and I am not a developer but a DBA. As such some examples would be greatly appreciated since I tend to understand better I compiled and installed the extension 1 - Exact matching without knowing the hierarchy, just the key and element, I built a set like col1 | col2 ------+-------------------------------------------------- 1 | {"Home Email": {"EmailAddress": "1@xxxxxxxxx"}} 2 | {"Home Email": {"EmailAddress": "2@xxxxxxxxx"}} 3 | {"Home Email": {"EmailAddress": "3@xxxxxxxxx"}} JSQuqery is super SELECT * FROM test1 WHERE col2 @@ '*.EmailAddress = "1@xxxxxxxxx"'; Now I can do a performance boost using CREATE INDEX idx1 ON test1 USING GIN (col2 jsonb_value_path_ops); I see this yield from testdb=# explain analyze^JSELECT * FROM test1 WHERE col2 @@ '*.EmailAddress = "10@xxxxxxxxx"'; Seq Scan on test1 (cost=0.00..12423.00 rows=500 width=68) (actual time=0.016..160.777 rows=1 loops=1) Filter: (col2 @@ '*."EmailAddress" = "10@xxxxxxxxx"'::jsquery) Rows Removed by Filter: 499999 Planning time: 0.042 ms Execution time: 160.799 ms (5 rows) to testdb-# SELECT * FROM test1 WHERE col2 @@ '*.EmailAddress = "10@xxxxxxxxx"'; Bitmap Heap Scan on test1 (cost=31.88..1559.32 rows=500 width=68) (actual time=0.018..0.019 rows=1 loops=1) Recheck Cond: (col2 @@ '*."EmailAddress" = "10@xxxxxxxxx"'::jsquery) Heap Blocks: exact=1 -> Bitmap Index Scan on idx1 (cost=0.00..31.75 rows=500 width=0) (actual time=0.011..0.011 rows=1 loops=1) Index Cond: (col2 @@ '*."EmailAddress" = "10@xxxxxxxxx"'::jsquery) Planning time: 0.039 ms Execution time: 0.038 ms (7 rows) A whooping 4000 times improvement But I also noticed a vodka index testdb=# CREATE INDEX idx2 ON testdb-# test1 USING vodka (col2); ERROR: access method "vodka" does not exist What am I missing ? 2 - Is there anyway I can accomplish a pattern and/or case insensitive search using JSQuery similar to select * from test2 where upper((col2 -> 'Home Email') ->> 'EmailAddress') ilike '%3%YAH%'; select * from test2 where (col2 -> 'Home Email') ->> 'EmailAddress' like '%3%yah%'; If so what indexing strategy can be used to have similar gains as above ? Many thanks for any help Armand |
