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.