How to bind dynamic JSON objects to PostgreSQL, using mongodb_fdw?

Question

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.

*But BSON, JSON or JSONB don't seem to be recognized by the FDW as SQL data types.* -- What did you tried? What was the error messge, if there was one? What was the (unexpected) output, if there was no error? — pozs, Feb 06 '15 at 14:12
According to [this similar feature request](https://github.com/citusdata/mongo_fdw/issues/19) the `mongo_fdw` you use won't be updated; but there is an [EnterpriseDB fork](https://github.com/EnterpriseDB/mongo_fdw) which allows you to use the `json` sql type (only that) where you have json array or object in your document (according to [their current sources](https://github.com/EnterpriseDB/mongo_fdw/blob/a159d42bd1b2190c47e8b97b5939415e07389d24/mongo_fdw.c#L1341)). — pozs, Feb 06 '15 at 14:55
Sweet, thanks for making sense to this! Will try the other fdw flavour. — akauppi, Feb 06 '15 at 15:10
@pozs I tried the other flavor. It was rather messy to build (on OS X + Homebrew), and only the 'legacy' sub-flavor offers JSON support in the code. Even so, I did not get to actually see my data using it. — akauppi, Feb 06 '15 at 16:45

score 1 · Accepted Answer · answered Feb 10 '15 at 15:43

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.

ref. https://github.com/EnterpriseDB/mongo_fdw/issues/20

How to bind dynamic JSON objects to PostgreSQL, using mongodb_fdw?

1 Answers1