diff options
author | Vicențiu Ciorbaru <vicentiu@mariadb.org> | 2017-09-19 12:06:50 +0300 |
---|---|---|
committer | Vicențiu Ciorbaru <vicentiu@mariadb.org> | 2017-09-19 12:06:50 +0300 |
commit | ec6042bda097fa53c43caf4a1acc32c5a77f6ed4 (patch) | |
tree | 751af6227789bfa916a6de426542a966845c8cd1 /storage/connect/mysql-test/connect/r/mongo_c.result | |
parent | ba0ee91077d9b89296328892aa5d5d293f4e0fb1 (diff) | |
parent | b337a0682943fa7e055380b7859762acf7fb588c (diff) | |
download | mariadb-git-ec6042bda097fa53c43caf4a1acc32c5a77f6ed4.tar.gz |
Merge branch '10.0' into 10.1
Diffstat (limited to 'storage/connect/mysql-test/connect/r/mongo_c.result')
-rw-r--r-- | storage/connect/mysql-test/connect/r/mongo_c.result | 380 |
1 files changed, 380 insertions, 0 deletions
diff --git a/storage/connect/mysql-test/connect/r/mongo_c.result b/storage/connect/mysql-test/connect/r/mongo_c.result new file mode 100644 index 00000000000..f90f3a94b44 --- /dev/null +++ b/storage/connect/mysql-test/connect/r/mongo_c.result @@ -0,0 +1,380 @@ +set connect_enable_mongo=1; +# +# Test the MONGO table type +# +CREATE TABLE t1 (Document varchar(1024) field_format='*') +ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants +OPTION_LIST='Driver=C,Version=0' DATA_CHARSET=utf8; +SELECT * from t1 limit 3; +Document +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51c"},"address":{"building":"1007","coord":[-73.856076999999999089,40.848447000000000173],"street":"Morris ParkAve", "zipcode":"10462"},"borough":"Bronx","cuisine":"Bakery","grades":[{"date":{"$date":1393804800000},"grade":"A","score":2},{"date":{"$date":1378857600000},"grade":"A","score":6},{"date":{"$date":1358985600000},"grade":"A","score":10},{"date":{"$date":1322006400000},"grade":"A","score":9},{"date":{"$date":1299715200000},"grade":"B","score":14}],"name":"Morris ParkBakeShop", "restaurant_id":"30075445"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51d"},"address":{"building":"469","coord":[-73.96170399999999745,40.66294200000000103],"street":"Flatbush Avenue", "zipcode":"11225"},"borough":"Brooklyn","cuisine":"Hamburgers","grades":[{"date":{"$date":1419897600000},"grade":"A","score":8},{"date":{"$date":1404172800000},"grade":"B","score":23},{"date":{"$date":1367280000000},"grade":"A","score":12},{"date":{"$date":1336435200000},"grade":"A","score":12}],"name":"Wendy'S","restaurant_id":"30112340"} +{"_id":{"$oid":"58ada47de5a51ddfcd5ed51e"},"address":{"building":"351","coord":[-73.985135599999992451,40.767691900000002647],"street":"West 57Street", "zipcode":"10019"},"borough":"Manhattan","cuisine":"Irish","grades":[{"date":{"$date":1409961600000},"grade":"A","score":2},{"date":{"$date":1374451200000},"grade":"A","score":11},{"date":{"$date":1343692800000},"grade":"A","score":12},{"date":{"$date":1325116800000},"grade":"A","score":12}],"name":"Dj ReynoldsPubAndRestaurant", "restaurant_id":"30191841"} +DROP TABLE t1; +# +# Test catfunc +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants CATFUNC=columns +OPTION_LIST='Level=1,Driver=C,Version=0' DATA_CHARSET=utf8 ; +SELECT * from t1; +Column_Name Data_Type Type_Name Column_Size Buffer_Length Decimal_Digits Nullable Bpath +_id 1 CHAR 24 24 0 0 +address_building 1 CHAR 10 10 0 0 address.building +address_coord 1 CHAR 512 512 0 0 address.coord +address_street 1 CHAR 38 38 0 0 address.street +address_zipcode 1 CHAR 5 5 0 0 address.zipcode +borough 1 CHAR 13 13 0 0 +cuisine 1 CHAR 64 64 0 0 +grades_0 1 CHAR 512 512 0 1 grades.0 +name 1 CHAR 98 98 0 0 +restaurant_id 1 CHAR 8 8 0 0 +DROP TABLE t1; +# +# Explicit columns +# +CREATE TABLE t1 ( +_id VARCHAR(24) NOT NULL, +name VARCHAR(255) NOT NULL, +cuisine VARCHAR(255) NOT NULL, +borough VARCHAR(255) NOT NULL, +restaurant_id VARCHAR(255) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants +CONNECTION='mongodb://localhost:27017' LRECL=1024 DATA_CHARSET=utf8 +OPTION_LIST='Driver=C,Version=0'; +SELECT * FROM t1 LIMIT 10; +_id name cuisine borough restaurant_id +58ada47de5a51ddfcd5ed51c Morris Park Bake Shop Bakery Bronx 30075445 +58ada47de5a51ddfcd5ed51d Wendy'S Hamburgers Brooklyn 30112340 +58ada47de5a51ddfcd5ed51e Dj Reynolds Pub And Restaurant Irish Manhattan 30191841 +58ada47de5a51ddfcd5ed51f Riviera Caterer American Brooklyn 40356018 +58ada47de5a51ddfcd5ed520 Tov Kosher Kitchen Jewish/Kosher Queens 40356068 +58ada47de5a51ddfcd5ed521 Brunos On The Boulevard American Queens 40356151 +58ada47de5a51ddfcd5ed522 Kosher Island Jewish/Kosher Staten Island 40356442 +58ada47de5a51ddfcd5ed523 Wilken'S Fine Food Delicatessen Brooklyn 40356483 +58ada47de5a51ddfcd5ed524 Regina Caterers American Brooklyn 40356649 +58ada47de5a51ddfcd5ed525 Taste The Tropics Ice Cream Ice Cream, Gelato, Yogurt, Ices Brooklyn 40356731 +DROP TABLE t1; +# +# Test discovery +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants +OPTION_LIST='Level=1,Driver=C,Version=0' DATA_CHARSET=utf8; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `_id` char(24) NOT NULL, + `address_building` char(10) NOT NULL `FIELD_FORMAT`='address.building', + `address_coord` varchar(512) NOT NULL `FIELD_FORMAT`='address.coord', + `address_street` char(38) NOT NULL `FIELD_FORMAT`='address.street', + `address_zipcode` char(5) NOT NULL `FIELD_FORMAT`='address.zipcode', + `borough` char(13) NOT NULL, + `cuisine` char(64) NOT NULL, + `grades_0` varchar(512) DEFAULT NULL `FIELD_FORMAT`='grades.0', + `name` char(98) NOT NULL, + `restaurant_id` char(8) NOT NULL +) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MONGO' `TABNAME`='restaurants' `OPTION_LIST`='Level=1,Driver=C,Version=0' `DATA_CHARSET`='utf8' +SELECT * FROM t1 LIMIT 5; +_id address_building address_coord address_street address_zipcode borough cuisine grades_0 name restaurant_id +58ada47de5a51ddfcd5ed51c 1007 Morris Park Ave 10462 Bronx Bakery {"date":{"$date":1393804800000},"grade":"A","score":2} Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d 469 Flatbush Avenue 11225 Brooklyn Hamburgers {"date":{"$date":1419897600000},"grade":"A","score":8} Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e 351 West 57 Street 10019 Manhattan Irish {"date":{"$date":1409961600000},"grade":"A","score":2} Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f 2780 Stillwell Avenue 11224 Brooklyn American {"date":{"$date":1402358400000},"grade":"A","score":5} Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 97-22 63 Road 11374 Queens Jewish/Kosher {"date":{"$date":1416787200000},"grade":"Z","score":20} Tov Kosher Kitchen 40356068 +DROP TABLE t1; +# +# Dropping a column +# +CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants DATA_CHARSET=utf8 +COLIST='{"projection":{"grades":0}}' OPTION_LIST='Driver=C,Version=0,level=0' ; +SELECT * FROM t1 LIMIT 10; +_id address borough cuisine name restaurant_id +58ada47de5a51ddfcd5ed51c {"building":"1007","coord":[-73.856076999999999089,40.848447000000000173],"street":"Morris ParkAve", "zipcode":"10462"} Bronx Bakery Morris Park Bake Shop 30075445 +58ada47de5a51ddfcd5ed51d {"building":"469","coord":[-73.96170399999999745,40.66294200000000103],"street":"Flatbush Avenue", "zipcode":"11225"} Brooklyn Hamburgers Wendy'S 30112340 +58ada47de5a51ddfcd5ed51e {"building":"351","coord":[-73.985135599999992451,40.767691900000002647],"street":"West 57Street", "zipcode":"10019"} Manhattan Irish Dj Reynolds Pub And Restaurant 30191841 +58ada47de5a51ddfcd5ed51f {"building":"2780","coord":[-73.982419999999990523,40.579504999999997494],"street":"Stillwell Avenue", "zipcode":"11224"} Brooklyn American Riviera Caterer 40356018 +58ada47de5a51ddfcd5ed520 {"building":"97-22","coord":[-73.860115199999995639,40.731173900000001709],"street":"63 Road", "zipcode":"11374"} Queens Jewish/Kosher Tov Kosher Kitchen 40356068 +58ada47de5a51ddfcd5ed521 {"building":"8825","coord":[-73.880382699999998408,40.764312400000001446],"street":"Astoria Boulevard", "zipcode":"11369"} Queens American Brunos On The Boulevard 40356151 +58ada47de5a51ddfcd5ed522 {"building":"2206","coord":[-74.137728600000002643,40.611957199999999091],"street":"Victory Boulevard", "zipcode":"10314"} Staten Island Jewish/Kosher Kosher Island 40356442 +58ada47de5a51ddfcd5ed523 {"building":"7114","coord":[-73.906850599999998508,40.619903399999998328],"street":"Avenue U", "zipcode":"11234"} Brooklyn Delicatessen Wilken'S Fine Food 40356483 +58ada47de5a51ddfcd5ed524 {"building":"6409","coord":[-74.005288999999990551,40.628886000000001388],"street":"11 Avenue", "zipcode":"11219"} Brooklyn American Regina Caterers 40356649 +58ada47de5a51ddfcd5ed525 {"building":"1839","coord":[-73.948260899999993967,40.640827100000002758],"street":"Nostrand Avenue", "zipcode":"11226"} Brooklyn Ice Cream, Gelato, Yogurt, Ices Taste The Tropics Ice Cream 40356731 +DROP TABLE t1; +# +# Specifying Jpath +# +CREATE TABLE t1 ( +_id VARCHAR(24) NOT NULL, +name VARCHAR(64) NOT NULL, +cuisine CHAR(200) NOT NULL, +borough CHAR(16) NOT NULL, +street VARCHAR(65) FIELD_FORMAT='address.street', +building CHAR(16) FIELD_FORMAT='address.building', +zipcode CHAR(5) FIELD_FORMAT='address.zipcode', +grade CHAR(1) FIELD_FORMAT='grades.0.grade', +score INT(4) NOT NULL FIELD_FORMAT='grades.0.score', +`date` DATE FIELD_FORMAT='grades.0.date', +restaurant_id VARCHAR(255) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME='restaurants' DATA_CHARSET=utf8 +OPTION_LIST='Driver=C,Version=0' ; +SELECT * FROM t1 LIMIT 1; +_id 58ada47de5a51ddfcd5ed51c +name Morris Park Bake Shop +cuisine Bakery +borough Bronx +street Morris Park Ave +building 1007 +zipcode 10462 +grade A +score 2 +date 2014-03-03 +restaurant_id 30075445 +SELECT name, street, score, date FROM t1 LIMIT 5; +name street score date +Morris Park Bake Shop Morris Park Ave 2 2014-03-03 +Wendy'S Flatbush Avenue 8 2014-12-30 +Dj Reynolds Pub And Restaurant West 57 Street 2 2014-09-06 +Riviera Caterer Stillwell Avenue 5 2014-06-10 +Tov Kosher Kitchen 63 Road 20 2014-11-24 +SELECT name, cuisine, borough FROM t1 WHERE grade = 'A' LIMIT 10; +name cuisine borough +Morris Park Bake Shop Bakery Bronx +Wendy'S Hamburgers Brooklyn +Dj Reynolds Pub And Restaurant Irish Manhattan +Riviera Caterer American Brooklyn +Kosher Island Jewish/Kosher Staten Island +Wilken'S Fine Food Delicatessen Brooklyn +Regina Caterers American Brooklyn +Taste The Tropics Ice Cream Ice Cream, Gelato, Yogurt, Ices Brooklyn +Wild Asia American Bronx +C & C Catering Service American Brooklyn +SELECT COUNT(*) FROM t1 WHERE grade = 'A'; +COUNT(*) +20687 +SELECT * FROM t1 WHERE cuisine = 'English'; +_id name cuisine borough street building zipcode grade score date restaurant_id +58ada47de5a51ddfcd5ed83d Tea And Sympathy English Manhattan Greenwich Avenue 108 10011 A 8 2014-10-23 40391531 +58ada47de5a51ddfcd5ed85c Tartine English Manhattan West 11 Street 253 10014 A 11 2014-08-14 40392496 +58ada47de5a51ddfcd5ee1f3 The Park Slope Chipshop English Brooklyn 5 Avenue 383 11215 B 17 2014-09-29 40816202 +58ada47de5a51ddfcd5ee7e4 Pound And Pence English Manhattan Liberty Street 55 10005 A 7 2014-02-11 41022701 +58ada47de5a51ddfcd5ee999 Chip Shop English Brooklyn Atlantic Avenue 129 11201 A 9 2014-10-08 41076583 +58ada47ee5a51ddfcd5efe3f The Breslin Bar & Dining Room English Manhattan West 29 Street 16 10001 A 13 2014-06-09 41443706 +58ada47ee5a51ddfcd5efe99 Highlands Restaurant English Manhattan West 10 Street 150 10014 A 12 2014-10-22 41448559 +58ada47ee5a51ddfcd5f0413 The Fat Radish English Manhattan Orchard Street 17 10002 A 12 2014-07-26 41513545 +58ada47ee5a51ddfcd5f0777 Jones Wood Foundry English Manhattan East 76 Street 401 10021 A 12 2014-12-03 41557377 +58ada47ee5a51ddfcd5f0ea2 Whitehall English Manhattan Greenwich Avenue 19 10014 Z 15 2015-01-16 41625263 +58ada47ee5a51ddfcd5f1004 The Churchill Tavern English Manhattan East 28 Street 45 10016 A 13 2014-08-27 41633327 +58ada47ee5a51ddfcd5f13d5 The Monro English Brooklyn 5 Avenue 481 11215 A 7 2014-06-03 41660253 +58ada47ee5a51ddfcd5f1454 The Cock & Bull English Manhattan West 45 Street 23 10036 A 7 2014-08-07 41664704 +58ada47ee5a51ddfcd5f176e Dear Bushwick English Brooklyn Wilson Avenue 41 11237 A 12 2014-12-27 41690534 +58ada47ee5a51ddfcd5f1e91 Snowdonia Pub English Queens 32 Street 34-55 11106 A 12 2014-10-28 50000290 +58ada47ee5a51ddfcd5f2ddc Oscar'S Place English Manhattan Hudson Street 466 10014 A 10 2014-08-18 50011097 +SELECT * FROM t1 WHERE score = building; +_id name cuisine borough street building zipcode grade score date restaurant_id +DROP TABLE t1; +# +# Specifying Filter +# +CREATE TABLE t1 ( +_id CHAR(24) NOT NULL, +name CHAR(64) NOT NULL, +borough CHAR(16) NOT NULL, +restaurant_id CHAR(8) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants DATA_CHARSET=utf8 +FILTER='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' +OPTION_LIST='Driver=C,Version=0' ; +SELECT name FROM t1 WHERE borough = 'Queens'; +name +La Baraka Restaurant +Air France Lounge +Tournesol +Winegasm +Cafe Henri +Bistro 33 +Domaine Wine Bar +Cafe Triskell +Cannelle Patisserie +La Vie +Dirty Pierres Bistro +Fresca La Crepe +Bliss 46 Bistro +Bear +Cuisine By Claudette +Paris Baguette +The Baroness Bar +Francis Cafe +Madame Sou Sou +Crepe 'N' Tearia +Aperitif Bayside Llc +DROP TABLE t1; +# +# Testing pipeline +# +CREATE TABLE t1 ( +name VARCHAR(64) NOT NULL, +borough CHAR(16) NOT NULL, +date DATETIME NOT NULL, +grade CHAR(1) NOT NULL, +score INT(4) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME='restaurants' DATA_CHARSET=utf8 +COLIST='{"pipeline":[{"$match":{"cuisine":"French"}},{"$unwind":"$grades"},{"$project":{"_id":0,"name":1,"borough":1,"date":"$grades.date","grade":"$grades.grade","score":"$grades.score"}}]}' +OPTION_LIST='Driver=C,Version=0,Pipeline=1' ; +SELECT * FROM t1 LIMIT 10; +name borough date grade score +Tout Va Bien Manhattan 2014-11-10 01:00:00 B 15 +Tout Va Bien Manhattan 2014-04-03 02:00:00 A 13 +Tout Va Bien Manhattan 2013-07-17 02:00:00 C 36 +Tout Va Bien Manhattan 2013-02-06 01:00:00 B 22 +Tout Va Bien Manhattan 2012-07-16 02:00:00 C 36 +Tout Va Bien Manhattan 2012-03-08 01:00:00 C 7 +La Grenouille Manhattan 2014-04-09 02:00:00 A 10 +La Grenouille Manhattan 2013-03-05 01:00:00 A 9 +La Grenouille Manhattan 2012-02-02 01:00:00 A 13 +Le Perigord Manhattan 2014-07-14 02:00:00 B 14 +SELECT name, grade, score, date FROM t1 WHERE borough = 'Bronx'; +name grade score date +Bistro Sk A 10 2014-11-21 01:00:00 +Bistro Sk A 12 2014-02-19 01:00:00 +Bistro Sk B 18 2013-06-12 02:00:00 +DROP TABLE t1; +# +# try level 2 discovery +# +CREATE TABLE t1 +ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME=restaurants +FILTER='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' +COLIST='{"projection":{"cuisine":0}}' +OPTION_LIST='Driver=C,level=2,version=0'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `_id` char(24) NOT NULL, + `address_building` char(6) NOT NULL `FIELD_FORMAT`='address.building', + `address_coord_0` double(12,6) NOT NULL `FIELD_FORMAT`='address.coord.0', + `address_street` char(25) NOT NULL `FIELD_FORMAT`='address.street', + `address_zipcode` char(5) NOT NULL `FIELD_FORMAT`='address.zipcode', + `borough` char(13) NOT NULL, + `grades_0_date` datetime NOT NULL `FIELD_FORMAT`='grades.0.date', + `grades_0_grade` char(14) NOT NULL `FIELD_FORMAT`='grades.0.grade', + `grades_0_score` int(11) NOT NULL `FIELD_FORMAT`='grades.0.score', + `name` char(32) NOT NULL, + `restaurant_id` char(8) NOT NULL +) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`='MONGO' `TABNAME`='restaurants' `COLIST`='{"projection":{"cuisine":0}}' `FILTER`='{"cuisine":"French","borough":{"$ne":"Manhattan"}}' `OPTION_LIST`='Driver=C,level=2,version=0' +SELECT name, borough, address_street, grades_0_score AS score FROM t1 WHERE grades_0_grade = 'B'; +name borough address_street score +Le Gamin Brooklyn Vanderbilt Avenue 24 +Bistro 33 Queens Ditmars Boulevard 15 +Dirty Pierres Bistro Queens Station Square 22 +Santos Anne Brooklyn Union Avenue 26 +Le Paddock Brooklyn Prospect Avenue 17 +La Crepe Et La Vie Brooklyn Foster Avenue 24 +Francis Cafe Queens Ditmars Boulevard 19 +DROP TABLE t1; +# +# try CRUD operations +# +false +CREATE TABLE t1 (_id INT(4) NOT NULL, msg CHAR(64)) +ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME='testcoll' +OPTION_LIST='Driver=C,Version=0' ; +DELETE FROM t1; +INSERT INTO t1 VALUES(0,NULL),(1,'One'),(2,'Two'),(3,'Three'); +SELECT * FROM t1; +_id msg +0 NULL +1 One +2 Two +3 Three +UPDATE t1 SET msg = 'Deux' WHERE _id = 2; +DELETE FROM t1 WHERE msg IS NULL; +SELECT * FROM t1; +_id msg +1 One +2 Deux +3 Three +DELETE FROM t1; +DROP TABLE t1; +true +# +# List states whose population is equal or more than 10 millions +# +false +CREATE TABLE t1 ( +_id char(5) NOT NULL, +city char(16) NOT NULL, +loc_0 double(12,6) NOT NULL `FIELD_FORMAT`='loc.0', +loc_1 char(12) NOT NULL `FIELD_FORMAT`='loc.1', +pop int(11) NOT NULL, +state char(2) NOT NULL) +ENGINE=CONNECT CONNECTION='mongodb://localhost:27017' TABLE_TYPE='MONGO' TABNAME='cities' +OPTION_LIST='Driver=C,Version=0' DATA_CHARSET='utf8'; +# Using SQL for grouping +SELECT state, sum(pop) AS totalPop FROM t1 GROUP BY state HAVING totalPop >= 10000000 ORDER BY totalPop DESC; +state totalPop +CA 29754890 +NY 17990402 +TX 16984601 +FL 12686644 +PA 11881643 +IL 11427576 +OH 10846517 +DROP TABLE t1; +# Using a pipeline for grouping +CREATE TABLE t1 (_id CHAR(2) NOT NULL, totalPop INT(11) NOT NULL) +ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME='cities' DATA_CHARSET=utf8 +COLIST='{"pipeline":[{"$group":{"_id":"$state","totalPop":{"$sum":"$pop"}}},{"$match":{"totalPop":{"$gte":10000000}}},{"$sort":{"totalPop":-1}}]}' +OPTION_LIST='Driver=C,Version=0,Pipeline=1' ; +SELECT * FROM t1; +_id totalPop +CA 29754890 +NY 17990402 +TX 16984601 +FL 12686644 +PA 11881643 +IL 11427576 +OH 10846517 +DROP TABLE t1; +true +# +# Test making array +# +CREATE TABLE t1 ( +_id int(4) NOT NULL, +item CHAR(8) NOT NULL, +prices_0 INT(6) FIELD_FORMAT='prices.0', +prices_1 INT(6) FIELD_FORMAT='prices.1', +prices_2 INT(6) FIELD_FORMAT='prices.2', +prices_3 INT(6) FIELD_FORMAT='prices.3', +prices_4 INT(6) FIELD_FORMAT='prices.4') +ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME='testcoll' DATA_CHARSET=utf8 +OPTION_LIST='Driver=C,Version=0' ; +INSERT INTO t1 VALUES +(1,'journal',87,45,63,12,78), +(2,'notebook',123,456,789,NULL,NULL), +(3,'paper',5,7,3,8,NULL), +(4,'planner',25,71,NULL,44,27), +(5,'postcard',5,7,3,8,NULL); +SELECT * FROM t1; +_id item prices_0 prices_1 prices_2 prices_3 prices_4 +1 journal 87 45 63 12 78 +2 notebook 123 456 789 NULL NULL +3 paper 5 7 3 8 NULL +4 planner 25 71 NULL 44 27 +5 postcard 5 7 3 8 NULL +DROP TABLE t1; +# +# Test array aggregation +# +CREATE TABLE t1 +ENGINE=CONNECT TABLE_TYPE=MONGO TABNAME='testcoll' +COLIST='{"pipeline":[{"$project":{"_id":0,"item":1,"total":{"$sum":"$prices"},"average":{"$avg":"$prices"}}}]}' +OPTION_LIST='Driver=C,Version=0,Pipeline=YES' ; +SELECT * FROM t1; +item total average +journal 285 57.000000 +notebook 1368 456.000000 +paper 23 5.750000 +planner 167 41.750000 +postcard 23 5.750000 +DROP TABLE t1; +true +set connect_enable_mongo=0; |