citusdb with mongo backend, subdocument and join

citusdb with mongo backend, subdocument and join

#Install as described:
#http://www.citusdata.com/downloads
#https://github.com/citusdata/mongo_fdw


-- create server object
CREATE SERVER mongo_server FOREIGN DATA WRAPPER mongo_fdw
OPTIONS (address '127.0.0.1', port '27017');

-- Table1:
CREATE FOREIGN TABLE table1
(
    _id NAME,
    anyid TEXT,
    events INTEGER
)
SERVER mongo_server
OPTIONS (database 'dm', collection 'aggregation');



-- Table2:
CREATE FOREIGN TABLE table2
(
    _id NAME,
    anyid TEXT,
    "category.subcategory.field" TEXT
)
SERVER mongo_server
OPTIONS (database 'scoring', collection 'scores');


Select events, "category.subcategory.field" from table1, table2 
where table1.anyid=table2.anyid limit 10;

 events   |  category.subcategory.field
----------+-----------------
       13 | A
       21 | A
       23 | B
      244 | C
        5 | C
       20 | C
       24 | A
        4 | A
        4 | C
        7 | C
(10 rows)