When we integrate third-party systems with Salesforce B2C Commerce Cloud using OCAPI or SCAPI, we often have the requirement to filter data based on date ranges or only retrieve data that has been modified after a certain time.
But how can we achieve this? Are there any other options available? Let’s explore the various filtering and query options in detail.
Querying
Not all endpoints are alike, but within the OCAPI the way of searching for different objects remains the same: making use of Queries and Filtering options.
Here are some of the example endpoints:
Attributes
Make sure to check the documentation pages for the specific endpoint to view the supported attributes before building your query.
Date Format
When crafting these date filters, adherence to the ISO 8601 date format (YYYY-MM-DDTHH:MM:SS.mmmZ) is essential for the API to parse the values correctly. Additionally, ensure that the field names, like creation_date, valid_from, valid_to, and others, correspond to your Salesforce Commerce Cloud data model’s actual date-related fields.
2012-03-19T07:22:59Z // example
Range Filter
If you need to find records that fall within a specific date interval, the range_filter is your go-to option. This filter can find records with a date value sitting between a specified start (from) and end (to) date.
{
"query": {
"filtered_query": {
"filter": {
"range_filter": {
"field": "creation_date",
"from": "2020-03-08T00:00:00.000Z",
"to": "2020-03-10T00:00:00.000Z"
}
},
"query": {
"match_all_query": {}
}
}
}
}
Range2 Filter
To deal with scenarios where you have two date fields and want to filter records with an overlapping date range, use the range2_filter. This allows the specification of a date range that overlaps the range between the two fields you are considering.
A Range2Filter allows you to restrict search results to hits where the first range (R1), defined by a pair of attributes (e.g., valid_from and valid_to), has a specific relationship to a second range (R2), defined by two values (from_value and to_value). The relationship between the two ranges is determined by the filter_mode, which can be one of the following:
overlap:R1overlaps fully or partially withR2containing:R1containsR2contained:R1is contained inR2
"query" : {
"filtered_query": {
"filter": {
"range2_filter": {
"from_field": "valid_from",
"to_field": "valid_to",
"filter_mode":"overlap",
"from_value": "2007-01-01T00:00:00.000Z",
"to_value": "2017-01-01T00:00:00.000Z"
}
},
"query": { "match_all_query": {} }
}
}
Bool Filter
Sometimes, the need for complexity arises when constructing date-based queries. The bool_filter permits the combination of numerous filters for complex logical expressions. This filter is specifically helpful for creating compound date queries that may, for instance, combine status checks with date ranges.
{
"query": {
"filtered_query": {
"query": {
"match_all_query": {}
},
"filter": {
"bool_filter": {
"operator": "and",
"filters": [
{
"term_filter": {
"field": "status",
"operator": "is",
"values": ["open"]
}
},
{
"range_filter": {
"field": "creation_date",
"from": "2023-01-01T00:00:00.000Z"
}
}
]
}
}
}
}
}
Term Query
For precision filtering, where a field must match an exact date, the term_query becomes the instrument of choice. This query matches records based on absolute equality with the specified date.
{
"query": {
"term_query": {
"fields": ["creation_date"],
"operator": "is",
"values": ["2023-04-01T00:00:00.000Z"]
}
}
}
Custom Endpoint
It is currently not fully available / BETA, but you can create custom GET endpoints tailored entirely to your requirements.
When creating these endpoints, it’s important to consider performance and caching – these are your responsibility when utilising this option.
Conclusion
The search API capabilities of OCAPI in Salesforce B2C Commerce Cloud offer robust and flexible options for date-related searches.
You can customize your searches using range_filter, range2_filter, bool_filter, and term_query as per your requirements. It is important to use the correct date format and field names to make the most out of these tools. These data querying capabilities can help you segment promotional data, manage catalog validity, or filter orders based on dates, making your commerce data handling more streamlined.
Happy coding!
