[ https://issues.jboss.org/browse/TEIID-3316?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-3316: ------------------------------------- There are multiple things wrong with the above test. - Teiid does not support multi-level nesting in 8.9.1, as I see it you have top level, "test" and "testarray". - Your schema does not reflect the your document. There is no link between Backup, test and TeiidArray. Those must be established using Foreign keys. If you used Teiid importer and it gave above schema then that is bug. Also Teiid MongoDB importer can not create multi-level tables like above yet. The posted schema shows single level, that means the data *MUST* to be like {code} {"TeiidArray":[ {"_id": "541087c3f706400030000002", "FirstName": "Eugene", "Score": [ "Korean", "100", "Medium" ], "LastName": "T", "CustomerID": 1 }, {"_id": "541089051c4d5a0030000001", "FirstName": "Stas", "Score": [ "Japanese", "50", "Advance" ], "LastName": "C", "CustomerID": 2 }, {"_id": "5410c969516cd50064000001", "FirstName": "Sally", "Score": [ "Russian", "90", "Advance" ], "LastName": "F", "CustomerID": 3 } ]} {code} then you can issue {code} select FirstName from TeiidArray {code} The schema that matches your document needs to look like {code} <vdb name="mongo" version="1"> <model name="mongo"> <source name="local" translator-name="mongodb" connection-jndi-name="java:/mongoDS"/> <metadata type="DDL"><![CDATA[ CREATE FOREIGN TABLE Backup ( BackupType String, TestDate date PRIMARY KEY ) OPTIONS(UPDATABLE 'TRUE'); CREATE FOREIGN TABLE Test ( ID String PRIMARY KEY, TestDate date, FirstName varchar(25), LastName varchar(25), CustomerID integer, Score object[], FOREIGN KEY (TestDate) REFERENCES Backup (TestDate) ) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:MERGE" 'Backup'); ]]> </metadata> </model> </vdb> {code} then you can run SQL like {code} insert into Backup(BackupType, TestDate) values ('DBBackup', {d '2015-01-29'}); insert into Test(ID, TestDate, FirstName, LastName, CustomerID, Score) values ('541087c3f706400030000002',{d '2015-01-29'}, 'Eugene', 'T', 1, ('Korean', '100', 'Medium')); insert into Test(ID, TestDate, FirstName, LastName, CustomerID, Score) values ('541089051c4d5a0030000001',{d '2015-01-29'}, 'Stas', 'C', 2, ('Japanese', '50', 'Advance')); insert into Test(ID, TestDate, FirstName, LastName, CustomerID, Score) values ('5410c969516cd50064000001',{d '2015-01-29'}, 'Sally', 'F', 3, ('Russian', '90', 'Advance')); {code} that will produce the JSON document in the MongoDB much closer to what you have {code} { "_id" : ISODate("2015-01-29T06:00:00Z"), "BackupType" : "DBBackup", "Test" : [ { "FirstName" : "Eugene", "LastName" : "T", "CustomerID" : 1, "Score" : [ "Korean", "100", "Medium" ], "_id" : "541087c3f706400030000002" }, { "FirstName" : "Stas", "LastName" : "C", "CustomerID" : 2, "Score" : [ "Japanese", "50", "Advance" ], "_id" : "541089051c4d5a0030000001" }, { "FirstName" : "Sally", "LastName" : "F", "CustomerID" : 3, "Score" : [ "Russian", "90", "Advance" ], "_id" : "5410c969516cd50064000001" }, { "FirstName" : "Eugene", "LastName" : "T", "CustomerID" : 1, "Score" : [ "Korean", "100", "Medium" ], "_id" : "541087c3f706400030000002" }, { "FirstName" : "Stas", "LastName" : "C", "CustomerID" : 2, "Score" : [ "Japanese", "50", "Advance" ], "_id" : "541089051c4d5a0030000001" }, { "FirstName" : "Sally", "LastName" : "F", "CustomerID" : 3, "Score" : [ "Russian", "90", "Advance" ], "_id" : "5410c969516cd50064000001" } ] } {code} If you notice there is no verbose "test" level, which was just array of array without any links between the documents. Teiid *needs* to have that relationship between documents, in the above I used "TestDate" field to build that. Hopefully this explains how to use Teiid, and not all the MongoDB documents can be read easily through Teiid, it expects certain rules. -- This message was sent by Atlassian JIRA (v6.3.11#6341)