search

EnterpriseDB / mongo_fdw

PostgreSQL foreign data wrapper for MongoDB
GNU Lesser General Public License v3.0
326 stars 70 forks source link

Nested Fields with mongo_fdw #38

Closed paulowoody closed 7 years ago

paulowoody commented 9 years ago

On a fresh install of Ubuntu Server 15.04 I have installed: MongoDB v2.6.3 (standard build from apt-get) PostgreSQL v9.4.4 (standard build from apt-get) Libbson v1.1.11 (from github.com/mongodb/libbson) and the current version of mongo_fdw via github, built using "--with-master" option.

Mapping fields in mongo documents to columns in postgresql is fine until I come to mapping nested fields, which do not raise an error in the CREATE FOREIGN TABLE statement, but which return empty values when queried from postgres.

e.g. in mongo:

{ "incident_id": "1234567", "timeline": { "incident": {"year": 2010, "month": 4, "day": 9 }}, "security_incident": "here is some text" }

and in postgres:

CREATE FOREIGN TABLE incidents ( _id name , incident_id text , "timeline.incident.year" integer , "timeline.incident.month" integer , "timeline.incident.day" integer , security_incident text ) SERVER mongo_server OPTIONS (database 'mydb', collection 'incidents');

When I run select * from incidents; the two top-level fields return correct data, but all of the nested fields (in timeline) are blank. What am I doing wrong, or is there an issue with using this version of postgresql?

Thanks! PS. I have also tried it with Mongo v3.0.6 but it makes no difference.

JoshdotNew commented 8 years ago

I am having the same problem, nested fields do not return. They were working when I was using the mongo_fdw from citusdata.

ahsanhadi commented 8 years ago

We will take a look and get back to you.

On Tue, Oct 20, 2015 at 5:08 PM, JoshdotNew notifications@github.com wrote:

I am having the same problem, nested fields do not return. They were working when I was using the mongo_fdw from citusdata.

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mongo_fdw/issues/38#issuecomment-149545515 .

Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company

Phone: +92-51-8358874 Mobile: +92-333-5162114

Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.

ibrarahmad commented 8 years ago

I have pushed the fix.

On Tue, Oct 20, 2015 at 5:29 PM, Ahsan Hadi notifications@github.com wrote:

We will take a look and get back to you.

On Tue, Oct 20, 2015 at 5:08 PM, JoshdotNew notifications@github.com wrote:

I am having the same problem, nested fields do not return. They were working when I was using the mongo_fdw from citusdata.

— Reply to this email directly or view it on GitHub < https://github.com/EnterpriseDB/mongo_fdw/issues/38#issuecomment-149545515

.

Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company

Phone: +92-51-8358874 Mobile: +92-333-5162114

Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mongo_fdw/issues/38#issuecomment-149550176 .

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

aberenyi commented 8 years ago

I've tested with both master and 4.0.0, still does not work for me :disappointed:

ibrarahmad commented 8 years ago

Ahsan, can you please check on your system.

On Tue, Nov 17, 2015 at 9:40 PM, Attila Berényi notifications@github.com wrote:

I've tested with both master and 4.0.0, still does not work for me [image: :disappointed:]

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mongo_fdw/issues/38#issuecomment-157425969 .

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

ahsanhadi commented 8 years ago

I have tested this with legacy driver and it seems to work with nested fields however i can reproduce the empty field issue with the new mongo C driver. Ibrar will fix this issue with new mongo C driver this week...

-- Ahsan

On Tue, Nov 17, 2015 at 10:37 PM, Ibrar Ahmed notifications@github.com wrote:

Ahsan, can you please check on your system.

On Tue, Nov 17, 2015 at 9:40 PM, Attila Berényi notifications@github.com wrote:

I've tested with both master and 4.0.0, still does not work for me [image: :disappointed:]

— Reply to this email directly or view it on GitHub < https://github.com/EnterpriseDB/mongo_fdw/issues/38#issuecomment-157425969

.

Ibrar Ahmed EnterpriseDB http://www.enterprisedb.com

— Reply to this email directly or view it on GitHub https://github.com/EnterpriseDB/mongo_fdw/issues/38#issuecomment-157447596 .

Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company

Phone: +92-51-8358874 Mobile: +92-333-5162114

Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.