My best for all in this list.
i'm trying to use FDW for MongoDB
using PostgreSQL 9.4 using pgdg yum
repo: yum install
postgresql94-server postgresql94-contrib mongo_fdw94
My S.O. is CentOS
9.7 64bits
All is ok with Postgres. I can create extension, foreign
server and
foreign table.
My problem is when the "columns" of the
MongoDB collection are a
document or array. In my Foreign Table, I have
only null values
returned in selects.
All of others columns returns its
values with no problem.
Can any help or advise me about this?
Thanks a
lot!
Lucio - Rio de Janeiro - Brazil
Please see below some info:
On
mongo i have this on collection:
{
"_id" :
ObjectId("56ae3a5c2e277c04c77beec7"),
"address" : {
"building" :
"469",
"coord" : [-73.961704, 40.662942],
"street" : "Flatbush
Avenue",
"zipcode" : "11225"
},
"borough" : "Brooklyn",
"cuisine" :
"Hamburgers",
"grades" : [{
"date" : ISODate("2014-12-30T00:00:00Z"),
"grade" : "A",
"score" : 8
}, {
"date" :
ISODate("2014-07-01T00:00:00Z"),
"grade" : "B",
"score" : 23
}, {
"date" : ISODate("2013-04-30T00:00:00Z"),
"grade" : "A",
"score" : 12
}, {
"date" : ISODate("2012-05-08T00:00:00Z"),
"grade" : "A",
"score"
: 12
}],
"name" : "Wendy'S",
"restaurant_id" : "30112340"
}
On
PostgreSQL I have this:
"_id" "address" "borough" "cuisine" "grades"
"name"
"restaurant_id"
"56ae3a5c2e277c04c77beec7" <null> "Brooklyn"
"Hamburgers" <null>
"Wendy'S" "30112340"
CREATE FOREIGN TABLE
mongo_fdw.restaurants (
_id NAME NOT NULL,
address JSON,
borough
VARCHAR,
cuisine VARCHAR,
grades VARCHAR, --> errors happens if I put
json here
name VARCHAR,
restaurant_id VARCHAR
)
SERVER
mongo_server
OPTIONS (
database 'test',
collection 'restaurants');
