With a Where Constraint
We want to retrieve data for all the employees living in California.
An Optic query like this one retrieves a row sequence containing all view columns if a specific column contains a specific value:
op.fromView('Employee', 'Profile')
.where(op.eq(op.col('State'), 'CA'))
.offsetLimit(0, 100)
.result();
We used this query to retrieve a row sequence of all 23 columns defined in our view with a row for each employee whose State column is CA. We limited our output to the first 100 results:
The Data Accessor Function
fromView()pulls data indexed for the viewProfileassociated with the schemaEmployeeinto 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
eq()is one of many Boolean Expression Functions. It returns TRUE if the result of its argument expressions is equal, FALSE otherwise.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 are row 1 and part of row 2 of the 100-row x 23-column result:
{
"Employee.Profile.GUID": "64f1827d-a2bb-40d0-8875-7fc1d03c311b",
"Employee.Profile.HiredDate": "2016-02-27",
"Employee.Profile.Gender": "male",
"Employee.Profile.Title": "Mr.",
"Employee.Profile.GivenName": "Walter",
"Employee.Profile.MiddleInitial": "S",
"Employee.Profile.Surname": "Williams",
"Employee.Profile.StreetAddress": "4201 Freed Drive",
"Employee.Profile.City": "Stockton",
"Employee.Profile.State": "CA", // State: CA
"Employee.Profile.ZipCode": "95202",
"Employee.Profile.Country": "US",
"Employee.Profile.EmailAddress": "WalterSWilliams@fleckens.hu",
"Employee.Profile.TelephoneNumber": "209-766-7233",
"Employee.Profile.TelephoneCountryCode": "1",
"Employee.Profile.Birthday": "3/20/69",
"Employee.Profile.NationalID": "623-98-7762",
"Employee.Profile.Point": "37.900497,-121.38312",
"Employee.Profile.BaseSalary": 86446,
"Employee.Profile.Bonus": 8645,
"Employee.Profile.Department": "R&D",
"Employee.Profile.Status": "Active - Regular Exempt (Full-time)",
"Employee.Profile.ManagerGUID": "695fdc37-42f1-4c19-9ba1-c4fe87454041"
}
{
"Employee.Profile.GUID": "9fc9ac69-1dcb-46c1-9e0b-2f5ca11758d6",
"Employee.Profile.HiredDate": "2014-06-22",
"Employee.Profile.Gender": "male",
"Employee.Profile.Title": "Mr.",
"Employee.Profile.GivenName": "James",
"Employee.Profile.MiddleInitial": "M",
"Employee.Profile.Surname": "Dusek",
"Employee.Profile.StreetAddress": "4947 Ella Street",
"Employee.Profile.City": "Concord",
"Employee.Profile.State": "CA" // State: CA
// ... (other 13 columns)
}
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 only rows returned have a value of
CAin theStatecolumn, as specified bywhere().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.