MongoDB: question about nested documents

[ichanjasper]

Currently, it seems like ID needs to be appear in parent and child for nested documents.  Would it be possible to flatten the data and put the objects from parent and child in one single table?  Therefore, user won't need to modify their mongo json structure in order to make Teiid to work with it.  In other words, user won't need to insert parent id in the child document.  Thanks.

[rareddy]

Typically 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..

[ichanjasper]

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

[rareddy]

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..