CODEWITHSUNDEEP
jsonpostgresqlpostgres-fdw
akauppi
akauppi

Reputation: 18066

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

The Foreign Data Wrapper for MongoDB is pretty awesome! I've gotten it to work using these instructions, apart from:

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.

Upvotes: 3

Views: 1310

Answers (1)

akauppi
akauppi

Reputation: 18066

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

Upvotes: 1

Related Questions