Skip to content

Commit 688e5ee

Browse files
akorotkovakorotkov
committed
Merge with master.
2 parents 1714bc3 + bfa87d1 commit 688e5ee

17 files changed

+2300
-764
lines changed

‎Makefile

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,9 @@
11
# contrib/jsquery/Makefile
22

33
MODULE_big = jsquery
4-
OBJS = jsquery_io.o jsquery_gram.o jsquery_op.o \
5-
jsquery_constr.o jsquery_extract.o jsonb_gin_ops.o \
6-
jsonb_vodka_ops.o
4+
OBJS = jsonb_gin_ops.o jsquery_constr.o jsquery_extract.o \
5+
jsquery_gram.o jsquery_io.o jsquery_op.o jsquery_support.o \
6+
jsonb_vodka_ops.o
77

88
EXTENSION = jsquery
99
DATA = jsquery--1.0.sql

‎data/test_jsquery.data

Lines changed: 1000 additions & 0 deletions
Large diffs are not rendered by default.

‎expected/jsquery.out

Lines changed: 309 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,7 @@
11
CREATE EXTENSION jsquery;
22
set escape_string_warning=off;
3+
CREATE TABLE test_jsquery (v jsonb);
4+
\copy test_jsquery from 'data/test_jsquery.data'
35
select 'asd.zzz = 13'::jsquery;
46
jsquery
57
------------------
@@ -828,3 +830,310 @@ select '"XXX"'::jsonb @@ '#.$="XXX"';
828830
f
829831
(1 row)
830832

833+
---table and index
834+
select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 > 0;
835+
count
836+
-------
837+
653
838+
(1 row)
839+
840+
select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 > 19;
841+
count
842+
-------
843+
13
844+
(1 row)
845+
846+
select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 < 19;
847+
count
848+
-------
849+
984
850+
(1 row)
851+
852+
select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 >= 19;
853+
count
854+
-------
855+
16
856+
(1 row)
857+
858+
select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 <= 19;
859+
count
860+
-------
861+
987
862+
(1 row)
863+
864+
select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 = 19;
865+
count
866+
-------
867+
3
868+
(1 row)
869+
870+
select count(*) from test_jsquery where (v->>'review_helpful_votes')::int4 > 16 AND
871+
(v->>'review_helpful_votes')::int4 < 20;
872+
count
873+
-------
874+
8
875+
(1 row)
876+
877+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0';
878+
count
879+
-------
880+
653
881+
(1 row)
882+
883+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 19';
884+
count
885+
-------
886+
13
887+
(1 row)
888+
889+
select count(*) from test_jsquery where v @@ 'review_helpful_votes < 19';
890+
count
891+
-------
892+
984
893+
(1 row)
894+
895+
select count(*) from test_jsquery where v @@ 'review_helpful_votes >= 19';
896+
count
897+
-------
898+
16
899+
(1 row)
900+
901+
select count(*) from test_jsquery where v @@ 'review_helpful_votes <= 19';
902+
count
903+
-------
904+
987
905+
(1 row)
906+
907+
select count(*) from test_jsquery where v @@ 'review_helpful_votes = 19';
908+
count
909+
-------
910+
3
911+
(1 row)
912+
913+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16' AND
914+
v @@ 'review_helpful_votes < 20';
915+
count
916+
-------
917+
8
918+
(1 row)
919+
920+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16 & review_helpful_votes < 20';
921+
count
922+
-------
923+
8
924+
(1 row)
925+
926+
select count(*) from test_jsquery where v @@ 'review_helpful_votes ($ > 16 & $ < 20)';
927+
count
928+
-------
929+
8
930+
(1 row)
931+
932+
select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"]';
933+
count
934+
-------
935+
7
936+
(1 row)
937+
938+
select count(*) from test_jsquery where v @@ 'similar_product_ids(# = "0440180295") ';
939+
count
940+
-------
941+
7
942+
(1 row)
943+
944+
select count(*) from test_jsquery where v @@ 'similar_product_ids.#($ = "0440180295") ';
945+
count
946+
-------
947+
7
948+
(1 row)
949+
950+
select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"] & product_sales_rank > 300000';
951+
count
952+
-------
953+
4
954+
(1 row)
955+
956+
create index t_idx on test_jsquery using gin (v jsonb_bloom_value_ops);
957+
set enable_seqscan = off;
958+
explain (costs off) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0';
959+
QUERY PLAN
960+
------------------------------------------------------------------------
961+
Aggregate
962+
-> Bitmap Heap Scan on test_jsquery
963+
Recheck Cond: (v @@ '"review_helpful_votes" > 0'::jsquery)
964+
-> Bitmap Index Scan on t_idx
965+
Index Cond: (v @@ '"review_helpful_votes" > 0'::jsquery)
966+
(5 rows)
967+
968+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0';
969+
count
970+
-------
971+
653
972+
(1 row)
973+
974+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 19';
975+
count
976+
-------
977+
13
978+
(1 row)
979+
980+
select count(*) from test_jsquery where v @@ 'review_helpful_votes < 19';
981+
count
982+
-------
983+
984
984+
(1 row)
985+
986+
select count(*) from test_jsquery where v @@ 'review_helpful_votes >= 19';
987+
count
988+
-------
989+
16
990+
(1 row)
991+
992+
select count(*) from test_jsquery where v @@ 'review_helpful_votes <= 19';
993+
count
994+
-------
995+
987
996+
(1 row)
997+
998+
select count(*) from test_jsquery where v @@ 'review_helpful_votes = 19';
999+
count
1000+
-------
1001+
3
1002+
(1 row)
1003+
1004+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16' AND
1005+
v @@ 'review_helpful_votes < 20';
1006+
count
1007+
-------
1008+
8
1009+
(1 row)
1010+
1011+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16 & review_helpful_votes < 20';
1012+
count
1013+
-------
1014+
8
1015+
(1 row)
1016+
1017+
select count(*) from test_jsquery where v @@ 'review_helpful_votes ($ > 16 & $ < 20)';
1018+
count
1019+
-------
1020+
8
1021+
(1 row)
1022+
1023+
select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"]';
1024+
count
1025+
-------
1026+
7
1027+
(1 row)
1028+
1029+
select count(*) from test_jsquery where v @@ 'similar_product_ids(# = "0440180295") ';
1030+
count
1031+
-------
1032+
7
1033+
(1 row)
1034+
1035+
select count(*) from test_jsquery where v @@ 'similar_product_ids.#($ = "0440180295") ';
1036+
count
1037+
-------
1038+
7
1039+
(1 row)
1040+
1041+
select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"] & product_sales_rank > 300000';
1042+
count
1043+
-------
1044+
4
1045+
(1 row)
1046+
1047+
drop index t_idx;
1048+
create index t_idx on test_jsquery using gin (v jsonb_hash_value_ops);
1049+
set enable_seqscan = off;
1050+
explain (costs off) select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0';
1051+
QUERY PLAN
1052+
------------------------------------------------------------------------
1053+
Aggregate
1054+
-> Bitmap Heap Scan on test_jsquery
1055+
Recheck Cond: (v @@ '"review_helpful_votes" > 0'::jsquery)
1056+
-> Bitmap Index Scan on t_idx
1057+
Index Cond: (v @@ '"review_helpful_votes" > 0'::jsquery)
1058+
(5 rows)
1059+
1060+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 0';
1061+
count
1062+
-------
1063+
653
1064+
(1 row)
1065+
1066+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 19';
1067+
count
1068+
-------
1069+
13
1070+
(1 row)
1071+
1072+
select count(*) from test_jsquery where v @@ 'review_helpful_votes < 19';
1073+
count
1074+
-------
1075+
984
1076+
(1 row)
1077+
1078+
select count(*) from test_jsquery where v @@ 'review_helpful_votes >= 19';
1079+
count
1080+
-------
1081+
16
1082+
(1 row)
1083+
1084+
select count(*) from test_jsquery where v @@ 'review_helpful_votes <= 19';
1085+
count
1086+
-------
1087+
987
1088+
(1 row)
1089+
1090+
select count(*) from test_jsquery where v @@ 'review_helpful_votes = 19';
1091+
count
1092+
-------
1093+
3
1094+
(1 row)
1095+
1096+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16' AND
1097+
v @@ 'review_helpful_votes < 20';
1098+
count
1099+
-------
1100+
8
1101+
(1 row)
1102+
1103+
select count(*) from test_jsquery where v @@ 'review_helpful_votes > 16 & review_helpful_votes < 20';
1104+
count
1105+
-------
1106+
8
1107+
(1 row)
1108+
1109+
select count(*) from test_jsquery where v @@ 'review_helpful_votes ($ > 16 & $ < 20)';
1110+
count
1111+
-------
1112+
8
1113+
(1 row)
1114+
1115+
select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"]';
1116+
count
1117+
-------
1118+
7
1119+
(1 row)
1120+
1121+
select count(*) from test_jsquery where v @@ 'similar_product_ids(# = "0440180295") ';
1122+
count
1123+
-------
1124+
7
1125+
(1 row)
1126+
1127+
select count(*) from test_jsquery where v @@ 'similar_product_ids.#($ = "0440180295") ';
1128+
count
1129+
-------
1130+
7
1131+
(1 row)
1132+
1133+
select count(*) from test_jsquery where v @@ 'similar_product_ids && ["0440180295"] & product_sales_rank > 300000';
1134+
count
1135+
-------
1136+
4
1137+
(1 row)
1138+
1139+
RESET enable_seqscan;

0 commit comments

Comments
 (0)