With a Join against Another View

We want to join data from our employee documents with data from documents containing department data—department documents—also already in our database.

So, we created a second view for our department documents in a separate TDE:

{
  "template": {
    "description": "Department Template",
    "context": "/",
    "collections": [
      "https://example.com/content/department" // Specifying our document collection
    ],
    "rows": [
      {
        "schemaName": "Department",            // Schema: Department
        "viewName": "Profile",                 // View: Profile
        "viewLayout": "sparse",
        "columns": [                           // Specifying our view's columns
          {
            "name": "Department",              // Column 1: Department
            "scalarType": "string",
            "val": "Department",
            "nullable": true,
            "invalidValues": "ignore"
          },
          {
            "name": "LineOfBusiness",
            "scalarType": "string",
            "val": "LineOfBusiness",
            "nullable": true,
            "invalidValues": "ignore"
          },
          {
            "name": "Description",
            "scalarType": "string",
            "val": "Description",
            "nullable": true,
            "invalidValues": "ignore"
          }
        ]
      }
    ]
  }
}

We specified that only documents from our department collection, http://example.com/content/department, are relevant to this template.
We named our schema Department.
We also named this view Profile. We can use the same view name in different schemas.
This view has only 3 columns: Department (also a column in our employee view), LineOfBusiness, and Description.

With two views, we can now do a join.

An Optic query like this one performs an inner join of two views on a matching column, with one view acting as the “left table”, the other as the “right table”:

const employees = op.fromView('Employee', 'Profile');
const department = op.fromView('Department', 'Profile');

employees
  .joinInner(department, op.on(employees.col('Department'), department.col('Department')))
  .offsetLimit(0, 100)
  .result();

We used this query to retrieve a row sequence with all columns from both views for employee documents where there is a department document with a matching Department column:

The Data Accessor Function fromView (‘Employee’, ‘Profile’) pulls data indexed for the view Profile associated with the schema Employee into a row sequence of this view’s columns.
The Data Accessor Function fromView (‘Department’, ‘Profile’) pulls data indexed for the view Profile associated with the schema Department into a row sequence of this view’s columns.
When you use more than one Data Accessor Function in a query, it is best practice to use a variable to represent each one to keep the query simple and readable. This practice also lets you use Expression Functions like employees.col('Department') to reference a view's columns.
The Operator Function joinInner returns all rows from both views if the specified columns match, considering the view specified first in the query to be the "left table" and the view specified second to be the "right table."
The Auxiliary Function on() lets you specify a join condition for join-type operator functions like joinInner().
col() identifies the column in its argument.
The Operator Function offsetLimit() restricts results returned. The first parameter specifies the number of results to skip; the second, the number of results to return. So, (0, 100) returns the first 100 results.
The Executor Function result() executes the query and returns the results as a row sequence.

Here is row 1 of the 100-row x 26-column result:

{
  "Employee.Profile.GUID": "095d4e63-4a1f-4fc1-b694-b681e2aa3ee0", // EC1
  "Department.Profile.Department": "Engineering",                  //  DC1 = EC21
  "Employee.Profile.HiredDate": "2019-06-12",                      // EC2
  "Department.Profile.LineOfBusiness": "Information Technology",   //  DC2
  "Employee.Profile.Gender": "male",                               // EC3
  "Department.Profile.Description": "Engineering involves ...",    //  DC3
  "Employee.Profile.Title": "Mr.",                                 // EC4
  "Employee.Profile.GivenName": "Elmer",                           // EC5, etc.
  "Employee.Profile.MiddleInitial": "H", 
  "Employee.Profile.Surname": "Morlan", 
  "Employee.Profile.StreetAddress": "3084 Bolman Court", 
  "Employee.Profile.City": "Springfield", 
  "Employee.Profile.State": "IL", 
  "Employee.Profile.ZipCode": "62701", 
  "Employee.Profile.Country": "US", 
  "Employee.Profile.EmailAddress": "ElmerHMorlan@rhyta.com", 
  "Employee.Profile.TelephoneNumber": "217-301-0206", 
  "Employee.Profile.TelephoneCountryCode": "1", 
  "Employee.Profile.Birthday": "7/31/37", 
  "Employee.Profile.NationalID": "333-82-1925", 
  "Employee.Profile.Point": "39.747955,-89.709328", 
  "Employee.Profile.BaseSalary": 47744, 
  "Employee.Profile.Bonus": 4774, 
  "Employee.Profile.Department": "Engineering",                    // EC21 = DC1
  "Employee.Profile.Status": "Active - Regular Exempt (Full-time)", 
  "Employee.Profile.ManagerGUID": "3ad0ffbc-3ade-4897-902b-718417a721f5"
}

This query returned the first 100 results as we specified in offsetLimit().
The rows are in an unspecified order, which could change between executions. You can specify row order with the orderBy() operator function.
The 26 columns are the 23 from our "left table," the Employee Profile view (EC#) plus the 3 from our "right table," the Department Profile view (DC#).
The columns are presented in the order we defined them in our view.
- To change the order for this query alone, put all the columns into the select() operator function in your desired order.
- To permanently change the column order, edit your TDE to change their order in your view.
To restrict the columns returned, use the select() operator function.

In this section:

Getting Started with Optic