[teiid-issues] [JBoss JIRA] (TEIID-3056) MongoDB: Put nested JSON documents in one single table

[Ramesh Reddy (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-3056?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12989181#comment-12989181 ] 

Ramesh Reddy commented on TEIID-3056:
-------------------------------------

Now that TEIID-2958 is available, this can be solved with a "import" option that user can define how he/she wants their existing documents be imported. However, the caveat is when the document is being written back to the mongodb store it will write back in the same flat organization without nesting. I do not believe that is the expecting behavior.

I am open to implementing this if above restriction is acceptable.

> MongoDB:  Put nested JSON documents in one single table
> -------------------------------------------------------
>
>                 Key: TEIID-3056
>                 URL: https://issues.jboss.org/browse/TEIID-3056
>             Project: Teiid
>          Issue Type: Enhancement
>      Security Level: Public(Everyone can see) 
>          Components: Misc. Connectors
>    Affects Versions: 8.7
>         Environment: MongoDB with Teiid
>            Reporter: Ivan Chan
>            Assignee: Ramesh Reddy
>              Labels: teiid
>
> Currently, it seems like ID needs to be appear in parent and child for nested documents.  But it is unlikely a case for MongoDB customers.  It would be nice if Teiid can flatten out data in one single table.  Therefore, users would not need to insert parent id in the child document in order for Teiid to work.
> I am thinking the following case.  For example I have a JSON object like this:
> {code} 
> Customer
> {
>   _id: 1374932,
>   FirstName: "John",
>   LastName: "Doe",
>   Address:  {
>        _id: 43839430,
>         Street: "123 Lane",
>         City: "New York",
>         State: "NY",
>         Zipcode: "12345"
>      }
> }
> {code}
>  
> In this case, customer._id doesn't exist inside Address.  There is no way to do join in regular SQL fashion.  But it would be nice if teiid can put the column from parent and child into a single table.  Maybe something similar to the following schema:
> {code}
> 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');
> {code}



--
This message was sent by Atlassian JIRA
(v6.2.6#6264)