mongodb teiid working but can't get embedded structure to work
afrieden Dec 16, 2014 1:54 PMHi all, we have a working mongodb vdb. I am able to get basic queries working
mongodb-vdb.xml:
<metadata type="DDL"> <![CDATA[ CREATE FOREIGN TABLE records ( record_id varchar(100) PRIMARY KEY, position integer, contig varchar(25), reference varchar(100), alternate varchar(100), variant boolean, ordered boolean, version varchar(100), barcode varchar(100) ) OPTIONS(UPDATABLE 'FALSE'); CREATE FOREIGN TABLE sample ( record_id varchar(100) PRIMARY KEY, name varchar(255), zygosity varchar(50), genotype varchar(25), CL boolean, DP integer, FOREIGN KEY (record_id) REFERENCES records (record_id) ) OPTIONS(UPDATABLE 'FALSE', "teiid_mongo:MERGE" 'records'); ]]> </metadata>
And I am able to successfully run a simple query:
SELECT * FROM records WHERE records.barcode='PATXXX' AND records.position=12345678;
However, when I try to do a join to the embedded structure:
SELECT * FROM records JOIN sample on records.record_id = sample.record_id WHERE records.barcode='PATXXX' AND records.position=12345678;
This gives the error of
WARNING: Failed to execute: SELECT * FROM records JOIN sample on records.records_id = sample.records.id WHERE records.barcode='PATXXX' AND records.position=12345678 because: Remote org.teiid.api.exception.query.QueryResolverException: Group specified is ambiguous, resubmit the query by fully qualifying the group name: sample org.teiid.jdbc.TeiidSQLException: Remote org.teiid.api.exception.query.QueryResolverException: Group specified is ambiguous, resubmit the query by fully qualifying the group name: sample
Any thoughts on how to fix this? Thanks!