-
1. Re: MongoDB: question about nested documents
rareddy May 12, 2014 11:34 PM (in response to ichanjasper)1 of 1 people found this helpfulTypically in these cases child id is composite of parent and child id together. We can possibly avoid having the parent id in child record as parent _id can be inferred, but that is no guarantee that that will make user's json document be used with Teiid with out any modifications. Teiid makes few assumptions about data model as how the data is stored to make it work with SQL and support some joins.
Ramesh..
-
2. Re: MongoDB: question about nested documents
ichanjasper May 13, 2014 1:17 PM (in response to rareddy)I am thinking the following case. For example I have a JSON object like this:
Customer
{
_id: 1374932,
FirstName: "John",
LastName: "Doe",
Address:
{
_id: 43839430,
Street: "123 Lane",
City: "New York",
State: "NY",
Zipcode: "12345"
}
}
In this case, customer._id doesn't exist inside Address. There is no way to do join in regular SQL fashion. Would it be possible to create a single table in this case? Maybe something similar to the following schema:
CREATE FOREIGN TABLE Customer (
CustomerId integer PRIMARY KEY,
FirstName varchar(25),
LastName varchar(25),
address__id integer,
address_Street varchar(50),
address_City varchar(25),
address_State varchar(25),
address_Zipcode varchar(6),
) OPTIONS(UPDATABLE 'TRUE');
Thanks,
Ivan
-
3. Re: Re: MongoDB: question about nested documents
rareddy May 13, 2014 3:41 PM (in response to ichanjasper)Supporting the JSON format as you showed, possibly yes. I would to take a deep look at code again. It was easy to manage FK relationship when identifier was specified at both ends. However there will be still two tables "customer" and "address", and "address" with "merge". You can log an enhancement request for this, I will take a look.
The single table structure you showed is valid (you can do this now), but it will create a flat JSON like below,
Customer { _id: 1374932, FirstName: "John", LastName: "Doe", address__id: 43839430, address_Street: "123 Lane", address_City: "New York", address_State: "NY", address_Zipcode: "12345" }
not a embedded doc like you hosed above. Here there is no need for JOIN, you can simply do "select * from customer", where as in previous one you need to JOIN tables. MongoDB does not force the table structure on you, it is upto the user to define the schema, then Teiid decides the storage format in MongoDB. That is reason building metadata from existing mongoDB schema is not straight forward and may not always with Teiid.
Ramesh..