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');