Introduction
In Kodexa, a robust and flexible structured query language enables users to filter documents efficiently. This guide provides an overview of how to leverage various operators, comparators, functions, and subqueries within Kodexa to perform detailed document queries.
Operators
Operators are the building blocks of queries in Kodexa, allowing you to combine, modify, or negate conditions. Here's how to use them:
AND (and): Combines two conditions that must both be true.
Example: status : 'active' and createdAt > '1-1-2000'
OR (or): Combines two conditions where at least one must be true.
Example: value ~ '%hello%' or name ~ '%world%'
NOT (not): Negates a condition, making the query true if the condition is false.
Example: not (id > 100 or category.order is null)
Comparators
Comparators are tools to compare expressions within your queries:
~ : Checks if a string expression is similar to another (supports wildcards * and %).
Example: http://catalog.name/ ~ 'electronic%'
: : Equality comparison.
Example: id : 5
! : Inequality comparison.
Example: username ! 'torshid'
>, <, >:, <: : Greater than, less than, greater than or equal to, and less than or equal to.
Example: distance > 100
is null, is not null : Checks if an expression is null or not null.
Example: status is null
is empty, is not empty : Checks if a collection is empty or not.
Example: children is empty
in : Checks if an expression is within a list of values.
Example: status in ('initialized', 'active')
Functions
Functions in Kodexa allow you to perform calculations or manipulate data:
Mathematical Functions: absolute(x), average(ratings), min(ratings), max(ratings), sum(a, b), diff(a, b), prod(a, b), quot(a, b), mod(a, b), sqrt(a)
Date and Time Functions: currentDate(), currentTime(), currentTimestamp()
String Functions: length(name), trim(name), upper(name), lower(name), concat(firstName, ' ', lastName)
Date Math Function
The dateMath function provides powerful date and time calculations within queries, allowing you to create dynamic time-based filters. This function accepts a string expression that describes temporal calculations relative to the current date and time.
Basic Syntax The dateMath function accepts a string parameter with the following format:
Start point: 'today', 'now', 'last <day>', or 'next <day>'
Followed by optional time adjustments using minus (-) operator
Starting Points
today: Starts at midnight (00:00:00) of the current date
Example: dateMath('today')
now: Uses the current date and time as the starting point
Example: dateMath('now')
last/next <day>: References the previous or next occurrence of a specific day
Example: dateMath('last Monday')
Example: dateMath('next Friday')
Time Adjustments After the starting point, you can subtract time units using the minus (-) operator:
Supported units: day(s), hour(s), minute(s), second(s), week(s), month(s), year(s)
Multiple adjustments can be chained
Example: dateMath('today - 1 day')
Example: dateMath('now - 2 hours - 30 minutes')
Example: dateMath('last Monday - 2 weeks')
Usage in Queries The dateMath function is particularly useful in date-based filtering scenarios:
Example: createdAt > dateMath('today - 7 days')
Finds documents created within the last week
Example: lastModified > dateMath('last Monday')
Finds documents modified since last Monday
Example: timestamp < dateMath('now - 1 hour')
Finds documents with timestamps older than one hour ago
Best Practices
Use 'today' when you need midnight-based calculations
Use 'now' when you need precision to the current time
Chain multiple time adjustments for complex date calculations
Consider using day-of-week calculations for business logic requiring specific days
Subqueries
Subqueries enhance the power of your queries by allowing conditions based on the results of nested queries:
exists: Returns true if the subquery condition is met.
Example: exists(employees.age > 60)
Explanation: Returns true if at least one employee is older than 60.
Best Practices
Use parentheses to control the precedence of operations, especially when mixing and and or operators.
Utilize wildcard characters wisely to optimize the performance of queries using the ~ comparator.
Employ functions to simplify complex calculations within your queries, reducing the need for external data processing.
Regularly update your query skills by revisiting this guide and exploring new functions and features as they are added to Kodexa.
Conclusion
Kodexa's structured query language provides a robust toolset for handling large document sets effectively. By learning these operators, comparators, functions, and subqueries, users can carry out complex data tasks within Kodexa. If you need more help, check the detailed guide or reach out to support.