Create a schema to be used with MongoDB and Teiid that has a mongo array in the document

Question

I have the following document in MongoDB:

{
    "_id" : ObjectId("529632350083bc14d145bb56"),
    "CaseId" : "8f68cf65-80ad-4950-88d5-e322d0296461",
    "CaseAddressString" : "527 Irving Avenue, Rivereno",
    "CaseOpenDatetime" : ISODate("2013-11-22T17:54:50.198Z"),
    "CaseBeginDatetime" : ISODate("2013-11-19T17:54:50.198Z"),
    "CaseDescription" : "Veniam deserunt nisi pariatur non commodo pariatur in.",
    "RequestorFirstName" : "Morgan",
    "RequestorLastName" : "Nunez",
    "age" : 41,
    "gender" : "female",
    "CasePhoneNumbers" : [
        {
            "type" : "home",
            "number" : "+1 (976) 478-2278"
        },
        {
            "type" : "fax",
            "number" : "+1 (801) 463-3144"
        }
    ],
    "location" : [
        -122.394372,
        37.765385
    ],
    "CaseLat" : 37.727456,
    "CaseLong" : -122.416738,
    "ServiceName" : "Missing Sign",
    "CaseCurrentStatusDatetime" : ISODate("0NaN-NaN-NaNTNaN:NaN:NaNZ")
}

The CasePhoneNumbers array is causing me some headaches. I'm trying to build a schema file that will work with Teiid. I have built the following and it works just fine in my project. Now to pull in the last part I'm at a loss.

CREATE FOREIGN TABLE cases(
    id varchar(50) PRIMARY KEY,
    CaseId varchar(50),
    CaseAddressString varchar(50),
    CaseOpenDatetime date,
    CaseBeginDatetime date,
    CaseDescription varchar(200),
    RequestorFirstName varchar(50),
    RequestorLastName varchar(50),
    CaseCurrentStatus varchar(25),
    age integer,
    gender varchar(20),
    CaseLat float,
    CaseLong float,
    ServiceName varchar(50)
) 
    OPTIONS(UPDATABLE 'TRUE');

It looks like Teiid support for Array data types (as object) has just been committed a few days ago: [TEIID-2848](https://issues.jboss.org/browse/TEIID-2848). The fixVersion suggests this is targeted for the upcoming Teiid 8.7.1 and 8.8 releases. — Stennie, Jul 05 '14 at 21:34

score 0 · Answer 1 · answered Aug 01 '14 at 15:17

0

You can do

CREATE FOREIGN TABLE cases(
    id varchar(50) PRIMARY KEY,
    CaseId varchar(50),
    CaseAddressString varchar(50),
    CaseOpenDatetime date,
    CaseBeginDatetime date,
    CaseDescription varchar(200),
    RequestorFirstName varchar(50),
    RequestorLastName varchar(50),
    CaseCurrentStatus varchar(25),
    age integer,
    gender varchar(20),
    CaseLat float,
    CaseLong float,
    ServiceName varchar(50)
) OPTIONS(UPDATABLE 'TRUE');

CREATE FOREIGN TABLE CasePhoneNumbers (
     caseid varchar,
     type string,
     number string,
     FOREIGN KEY (caseid) REFERENCES cases (_id)
) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:MERGE" 'cases')

You can see more documentation at https://docs.jboss.org/author/display/TEIID/MongoDB+Translator

https://issues.jboss.org/browse/TEIID-3040 removes th verbose IDREF fields, but this will be in Teiid 8.9

answered Aug 01 '14 at 15:17

Ramesh Reddy

554
1
3
8

Thank you for the posts. My hope is that we can update the app to use the newer version of Teiid. – user179981 Aug 05 '14 at 20:28
Is this feature supported in teiid-spring-boot runtime? Do I need to create the entity (view) class with @SelectQuery? – limcheekin Dec 10 '19 at 07:22
Yes, MongoDB support is added in teiid-spring-boot. You can choose to add @SelectQuery or you can also define a VDB in an old fashioned way, either would work. – Ramesh Reddy Dec 10 '19 at 13:09
Thanks for quick response. But the embedded documents like the CasePhoneNumbers above doesn't show in OData entities when expose it using org.teiid:spring-odata. Is this a known limitation or I need to do something to enable it? Do I need to write @SelectQuery for each property such as "type" and "number" to expose it as OData? – limcheekin Dec 13 '19 at 07:16
Check how `CasePhoneNumbers` is exposed in Teiid, if it is exposed as another table, then you can use the OData's `expand` capability to see the related information. If not we need to investigate why `CasePhoneNumbers` is exposed – Ramesh Reddy Dec 13 '19 at 14:25
Currently I check the exposure of entities using odata/$metadata, how do I check in Teiid of spring boot runtime? – limcheekin Dec 17 '19 at 08:49
Alright, let me open a new post below for my findings as it is too long to fit here. – limcheekin Dec 18 '19 at 03:27
If you enabled JDBC, then you could connect using the any JDBC SQL client and look at metadata and verify. If it is there I am guessing OData should have exposed already as entity or complex type. That tells me something else is an issue. Maybe a testcase to duplicate would help – Ramesh Reddy Dec 19 '19 at 22:32
Thanks man. Just find out that I can enabled JDBC by adding "teiid.jdbc-enable=true" to the application.properties file. I see the "rows" table using JDBC client, but the "rows" table doesn't exposed as OData entity. I will create a github repo to show the issue and assumed that you have MongoDB installed in your machine. Thanks for your help. :) – limcheekin Dec 20 '19 at 09:29
The github repo is created to showcase the issue at https://github.com/limcheekin/teiid-spring-boot-mongodb-odata. Please go thru the readme for more information. Hope to hear from you soon. Thanks. – limcheekin Dec 23 '19 at 04:04

score 0 · Answer 2 · answered Dec 18 '19 at 03:41

Not sure whether it is relevant, I saw the following warning message on my console:

WARN 11524 --- [main] org.teiid.CONNECTOR: TEIID30003 Without required support 
property TableAlias, pushdown will not be enabled for SelfJoins on translator 
[mongodb, raasagent].

"raasagent" is the MongoDBConnectionFactory bean I defined in DataSources.java.

Given the following document in MongoDB matrix_variable collection:

{
    "_id" : ObjectId("5b2367c9c9e77c0007ca0111"),
    "createdOn" : ISODate("2018-06-15T07:16:25.459Z"),
    "modifiedOn" : ISODate("2018-07-02T02:04:16.661Z"),
    "variableId" : "183cf101d2334ac09bd0db9aee96081f",
    "name" : "lfzq_test",
    "description" : "",
    "lastModifiedUserEmail" : "admin1@abc.com",
    "headerRow" : {
        "rowId" : "0b002942a5b4424f98fcc270af0290c3",
        "values" : [ 
            "name", 
            "value"
        ],
        "isEncrypted" : [ 
            false, 
            true
        ]
    },
    "rows" : [ 
        {
            "rowId" : "940467f9bb724d1688086baeec05049b",
            "values" : [ 
                "lfzq123q", 
                "751c634ca5e24102a00800b386955a5c"
            ]
        }
    ],
    "deleted" : false
}

The "headerRow" property is exposed as separate entity type in OData schema, but not "rows" property.

I found the following entity types with odata/$metadata:

<EntityType Name="headerRow">...</EntityType>
<EntityType Name="matrix_variable">...</EntityType>

Hope it is clear enough. Any clue why "rows" property is not exposed via OData?

The reason the "rows" not showing up, is because Teiid looks at the relation as "one to many" but it does not have Primary Key (PK) (no _id). Here "rows" does not have a primary key, thus even though it can be queried from JDBC, OData does not expose without PK. On "headerRow" it uses parent level document's key as its PK. So, you can either provide PK in rows, or Teiid needs to implement ComplexTypes support in EntityType. Another enhancement can be done is configure a regex for selection of PK. Log a ticket for either one. — Ramesh Reddy, Dec 24 '19 at 03:06
Thanks. I opened a ticket for the first solution as I can't change the data. Do feel free to edit it at https://issues.redhat.com/browse/TEIID-5873. Merry X'mas! :) — limcheekin, Dec 24 '19 at 04:47

Create a schema to be used with MongoDB and Teiid that has a mongo array in the document

2 Answers2