The Foreign Data Wrapper for MongoDB is pretty awesome! I've gotten it to work using these instructions, apart from:
an object with dynamic fields within it - which PostgreSQL type to use for such?
{ "key1": some, ... }
an array of objects - which PostgreSQL type to use for such? The length of the array may vary, but the objects are uniform in their inner structure.
[ { "a": 1 }, { "a": 2 }, { "a": 3 } ]
I found these slides on JSON capabilities in recent PostgreSQL versions. Neat. But BSON, JSON or JSONB don't seem to be recognized by the FDW as SQL data types.
If I use:
CREATE FOREIGN TABLE t6
(
"aaa.bbb" JSON -- 'bbb' is an array of JSON objects
)
SERVER mongo_server OPTIONS(...);
SELECT "aaa.bbb" AS bbb FROM t6;
I get:
psql:6.sql:152: ERROR: cannot convert bson type to column type
HINT: Column type: 114
The normal types TEXT, FLOAT etc. work.
The EnterpriseDB fork does it, as @pozs was pointing out. Just mark your data as JSON type.
However, the build system is rather bizarre to my taste, and does not really give you right errors for missing build components (it's obviously Linux-based and simply expects you to have a bunch of tools without properly checking for them).
Here's how I managed to build it on OS X + Homebrew:
$ brew install libtool libbson autoconf automake
$ ./autogen.sh --with-legacy
Note that the --with-meta variant does not provide JSON support, which was the reason I went for this fork anyways.