With a Date Constraint

We want to retrieve all the data for employees hired on or after January 1, 2022. We want the data in order from most to least recent hire.

When we built our view, we set the scalarType of the column HiredDate to date so we could treat it as a date value—

{
  "name": "HiredDate",       // Column 2
  "scalarType": "date",      // Date is type "date" in view
  "val": "HiredDate",
  "nullable": true,
  "invalidValues": "ignore"
}

—even though the HiredDate property in our document is a string:

{
  "HiredDate": "2012-09-15"  // Date is type "string" in document
}

An Optic query like this one retrieves a row sequence containing all view columns and any rows with a specified column containing a value equal to or greater than a specified value in descending order by that column:

op.fromView('Employee', 'Profile')
  .where(op.ge(op.col('HiredDate'), xs.date('2022-01-01')))
  .orderBy(op.desc('HiredDate'))
  .offsetLimit(0, 100)
  .result();

We used this query to retrieve a row sequence with all 23 columns defined in our view for each employee with a HiredDate greater than or equal to 2022-01-01, with rows in descending order by HiredDate. We limited our output to the first 100 results:

The Data Accessor Function fromView() pulls data indexed for the view Profile associated with the schema Employee into a row sequence of this view’s columns.
The Operator Function where() restricts the rows returned to only those that satisfy the given Boolean expression.
The Operator Function ge() is one of many Boolean Expression Functions. It returns TRUE if the result of its first argument expression is greater than or equal to the second, FALSE otherwise.
col() identifies the column in its argument.
xs.date() is the MarkLogic standard function for converting a string into a date value, the same data type as HiredDate.
The Operator Function orderBy() sorts its input row sequence into the order specified.
The Auxiliary Function desc() sorts rows in descending order by the specified column's value. The default order is ascending. The Auxiliary Function asc() explicitly sorts in ascending order.
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 are row 1, row 2, and part of row 3 of the 85-row x 23-column result:

{
  "Employee.Profile.GUID": "87e92c81-f014-44e3-821e-89e25302ec33", 
  "Employee.Profile.HiredDate": "2022-11-07",                    // HiredDate: 2022-11-07
  "Employee.Profile.Gender": "male", 
  "Employee.Profile.Title": "Mr.", 
  "Employee.Profile.GivenName": "Jack", 
  "Employee.Profile.MiddleInitial": "V", 
  "Employee.Profile.Surname": "Lee", 
  "Employee.Profile.StreetAddress": "67 Smith Road", 
  "Employee.Profile.City": "Cumming", 
  "Employee.Profile.State": "GA", 
  "Employee.Profile.ZipCode": "30130", 
  "Employee.Profile.Country": "US", 
  "Employee.Profile.EmailAddress": "JackVLee@jourrapide.com", 
  "Employee.Profile.TelephoneNumber": "770-887-9223", 
  "Employee.Profile.TelephoneCountryCode": "1", 
  "Employee.Profile.Birthday": "9/22/90", 
  "Employee.Profile.NationalID": "252-74-9314", 
  "Employee.Profile.Point": "34.106709,-84.226639", 
  "Employee.Profile.BaseSalary": 50814, 
  "Employee.Profile.Bonus": 5081, 
  "Employee.Profile.Department": "Sales", 
  "Employee.Profile.Status": "Active - Regular Exempt (Full-time)", 
  "Employee.Profile.ManagerGUID": "07bb968f-e95e-4c40-8889-d88ba6b369d0"
}
{
  "Employee.Profile.GUID": "33457955-6697-43d6-81f4-b1c12218fa01", 
  "Employee.Profile.HiredDate": "2022-11-03",                    // HiredDate: 2022-11-03 
  "Employee.Profile.Gender": "female", 
  "Employee.Profile.Title": "Mrs.", 
  "Employee.Profile.GivenName": "Patricia", 
  "Employee.Profile.MiddleInitial": "M", 
  "Employee.Profile.Surname": "Diaz", 
  "Employee.Profile.StreetAddress": "4536 Norma Avenue", 
  "Employee.Profile.City": "Marysville", 
  "Employee.Profile.State": "OH", 
  "Employee.Profile.ZipCode": "43040", 
  "Employee.Profile.Country": "US", 
  "Employee.Profile.EmailAddress": "PatriciaMDiaz@armyspy.com", 
  "Employee.Profile.TelephoneNumber": "937-209-8542", 
  "Employee.Profile.TelephoneCountryCode": "1", 
  "Employee.Profile.Birthday": "8/7/62", 
  "Employee.Profile.NationalID": "297-28-8599", 
  "Employee.Profile.Point": "40.172447,-83.289566", 
  "Employee.Profile.BaseSalary": 49297, 
  "Employee.Profile.Bonus": 4930, 
  "Employee.Profile.Department": "R&D", 
  "Employee.Profile.Status": "Active - Regular Exempt (Full-time)", 
  "Employee.Profile.ManagerGUID": "695fdc37-42f1-4c19-9ba1-c4fe87454041"
}
{
  "Employee.Profile.GUID": "8b7f98f0-60f2-4e40-bd01-bb0e2b30d99e", 
  "Employee.Profile.HiredDate": "2022-11-01"                     // HiredDate: 2022-11-01
// ... (other 21 columns)
}

This query returned 85 results, fewer than the 100 we specified in our offsetLimit(). Therefore, there were only 85 employees hired on or after January 1, 2022.
The rows are in descending order according to the HiredDate column.
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

With a Date Constraint

Search results