7 Replies Latest reply on Dec 19, 2014 12:36 PM by rareddy

    two tiered embedded structure

    afrieden

      Hi, I currently have the following mongodb translator:

       

      CREATE FOREIGN TABLE records (
      record_id varchar(100) PRIMARY KEY,
      position integer,
      contig varchar(25),
      reference varchar(100),
      alternate varchar(100),
      variant boolean,
      ordered boolean,
      version varchar(100),
      barcode varchar(100)
      ) OPTIONS(UPDATABLE 'FALSE');
      
      
      CREATE FOREIGN TABLE sample (
      record_id varchar(100) PRIMARY KEY,
      name varchar(255),
      zygosity varchar(50),
      genotype varchar(25),
      CL boolean,
      DP integer,
      FOREIGN KEY (record_id) REFERENCES records (record_id)
      ) OPTIONS(UPDATABLE 'FALSE', "teiid_mongo:MERGE" 'records');
      
      
      CREATE FOREIGN TABLE SQS1 (
      record_id varchar(100) PRIMARY KEY,
      score double,
      threshold varchar(100),
      pass boolean,
      FOREIGN KEY (record_id) REFERENCES sample (record_id)
                  ) OPTIONS(UPDATABLE 'FALSE', "teiid_mongo:MERGE" 'sample');
      ]]>
      

       

      Where I am trying to create this document structure:

       

      {
        _id : "548ef20924accecf495175cc",
        position: 12345678,
        contig : 'chr7',
         reference : 'A',
         alternate : 'G',
         variant: true,
         ordered : true,
         version : 'myversion',
         barcode : 'mybarcode',
         same : {
            name : 'mysample',
            zygosity : 'myzygosity',
            genotype : 'COMPLEX',
            CL : false,
            DP : 50,
            SQS1 : {
                 score: 6, 
                 threshold: 5, 
                 pass:  true  
            }
         }
      }
      

       

      I have been trying to run queries against this (worked for the first join but not the second).  Is this the correct translation?  Thanks!

        • 1. Re: two tiered embedded structure
          rareddy

          Alex,

           

          Nested MERGES are not supported feature yet, there is JIRA logged for this feature [TEIID-2561] Allow nested MERGE of entities in MongoDB translators - JBoss Issue Tracker

           

          I know I have written some support for it, but later retracted to focus more on other features. It can get quite complex in terms of generating queries for MongoDB. I do not know, if I would have enough time in 8.10 time frame, but if you want submit a patch I am willing take a look.

           

          Thanks


          Ramesh..

          • 2. Re: two tiered embedded structure
            afrieden

            Thanks Ramesh, is there a current workaround for this at the moment?  I'm not sure how I would write a patch for this ATM. 

            • 3. Re: two tiered embedded structure
              rareddy

              Since both Sample and SQS1 are both joined as through "record_id", you can make them sibling documents, or combine into single table. However, it will not give the JSON structure you shown above.

              • 4. Re: two tiered embedded structure
                afrieden

                the record_id join was just what was done in one of the examples.  I am happy to try another way to translate the document structure.  What would be an example of flattening out the embedded structure of sample and SQS1?  Thanks!

                • 5. Re: two tiered embedded structure
                  rareddy

                  What would be an example of flattening out the embedded structure of sample and SQS1?  Thanks!

                  Define as single table?

                  • 6. Re: two tiered embedded structure
                    afrieden

                    How would I do this in the translator since they are embedded?  Thanks!

                    • 7. Re: two tiered embedded structure
                      rareddy

                      Are using the (a) existing document structure in MongoDB or you are (b) creating a new document structure with Teiid. With (a) there is no good workaround currently, you can possibly treat the sub-document as an object or clob, with (b) you can define the table structure in the VDB like examples in the documentation, for example

                       

                      1. CREATE FOREIGN TABLE SQS1 ( 
                      2. record_id varchar(100) PRIMARY KEY, 
                      3. score double, 
                      4. threshold varchar(100), 
                      5. pass boolean, 
                      6. FOREIGN KEY (record_id) REFERENCES sample (record_id) 
                      7.             ) OPTIONS(UPDATABLE 'FALSE', "teiid_mongo:MERGE" 'records');