Building Optic Queries

Producing rows and columns for SQL analytics tools is a popular Optic task: a good place to start, since it will feel most familiar to those coming from a relational database world.

We want to query HR data from documents containing employee data—employee documents—that are already in our database.

One of Optic's Data Accessor Functions, fromView(), together with a MarkLogic index-definition document called a TDE (for Template Driven Extraction), allows us to treat our data almost as if it were coming from and going into a relational database table.

The TDE specifies which document set(s) to extract data from. It also defines the view's row columns to pull the extracted data into.

Upon detecting a new TDE, MarkLogic reindexes to create an index for each view in the TDE, populated with the document data for each column defined.

For these examples, we built a TDE to pull data from our employee documents that we had placed in our employee collection, http://example.com/content/employee, into a view we called Profile: our employee profile.

Here are the parts of our TDE relevant to most of the queries in this section. Other relevant parts will be called out as needed:

// Employee TDE

  "template": {
    "description": "Employee Template",
    "context": "/",
    "collections": [
      "https://example.com/content/employee" // Specifying our document collection
    ],
    "rows": [
      {
        "schemaName": "Employee",            // Schema: Employee
        "viewName": "Profile",               // View: Profile
        "viewLayout": "sparse",
        "columns": [                         // Specifying our view's columns
          {
            "name": "GUID",                  // Column 1: GUID
            "scalarType": "string",
            "val": "GUID",                   // GUID's value comes from
            "nullable": true,                //   document element, GUID
            "invalidValues": "ignore"
          },
// Columns 2 - 6 not shown
          {
            "name": "Surname",               // Column 7: Surname
            "scalarType": "string",
            "val": "Surname",
            "nullable": true,
            "invalidValues": "ignore"
          },
// Columns 8 - 9 not shown
          {
            "name": "State",                 // Column 10: State
            "scalarType": "string",
            "val": "State",
            "nullable": true,
            "invalidValues": "ignore"
          },
// Columns 11 - 20 not shown
          {
            "name": "Department",            // Column 21: Department
            "scalarType": "string",
            "val": "Department",
            "nullable": true,
            "invalidValues": "ignore"
          } // ,
// Columns 22 - 23 not shown
        ]
      }
    ]
  };

We specified that only documents from our employee collection, http://example.com/content/employee, are relevant to this TDE.
We defined one virtual row in this TDE:
- schemaName: We named our schema Employee. Using a meaningful schemaName lets us create an association among any views from certain types of documents no matter which TDE they are in.
- viewName: We named our virtual row, or view, Profile, since it includes those properties from our documents that we want in our employee profile.
- columns[]: We defined our view’s columns from our documents’ available properties in the order we needed them for our Employee Profile (23 total):
  - The 4 columns relevant to most queries in this section are GUID (column 1), Surname (column 7), State (column 10), and Department (column 21).
  - Other columns will be called out as needed.
- We could also have created other views such as Payroll, Benefits, and Reviews associated with our schema, Employee , containing different subsets of our documents’ properties.

We now have a view to use in fromView():

fromView(‘Employee’, ‘Profile’).

So, analogous to the SQL line

FROM Employee.Profile,

which accesses a particular table in a particular database, the data accessor function

fromView(‘Employee’, ‘Profile’)

lets Optic generate the correct row sequence to work with.

We can now build queries using our view.

Note

To create your own TDEs, see Template Driven Extraction (TDE) in the Application Developer's Guide.

In this section:

Getting Started with Optic