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

    two tiered embedded structure

    Alexander Frieden Newbie

      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!