Introduction
FlexibleSearch is one of the most frequently used features in SAP Commerce development.
Whether you are developing a DAO, troubleshooting production data, building a CronJob, implementing a Service Layer operation, or preparing for a SAP Commerce interview, you will almost certainly work with FlexibleSearch.
A typical FlexibleSearch query looks like:
SELECT {pk}
FROM {Product}
WHERE {code} = 'PRODUCT-001'At first glance, FlexibleSearch looks very similar to SQL.
However, there are important differences.
Instead of directly querying database tables and columns, FlexibleSearch primarily works with SAP Commerce item types and attributes.
SAP describes FlexibleSearch as an SQL-based search language for SAP Commerce items. The platform first resolves the FlexibleSearch syntax and then executes the resulting database query. (SAP Help Portal)
What is FlexibleSearch?
FlexibleSearch is SAP Commerce's built-in query language for retrieving data.
It allows developers to query objects such as:
Product
Customer
Order
OrderEntry
Cart
Category
Media
PriceRow
CatalogVersionInstead of writing:
SELECT *
FROM products
WHERE p_code = 'P001'you typically write:
SELECT {pk}
FROM {Product}
WHERE {code} = 'P001'The major advantage is that the query works with the SAP Commerce Type System rather than requiring developers to directly depend on physical database table names.
FlexibleSearch vs SQL
| FlexibleSearch | SQL |
|---|---|
| Uses SAP Commerce item types | Uses database tables |
| Uses SAP Commerce attributes | Uses database columns |
Uses {} syntax | Usually no {} |
| Understands the Type System | Database-specific |
| Works through SAP Commerce APIs | Direct database access |
| Supports SAP Commerce-specific features | Standard database language |
For example:
SQL
SELECT p_pk
FROM products
WHERE p_code = 'P001'FlexibleSearch
SELECT {pk}
FROM {Product}
WHERE {code} = 'P001'Basic FlexibleSearch Syntax
The basic structure is:
SELECT {attribute}
FROM {ItemType}
WHERE {attribute} = valueFor example:
SELECT {pk}
FROM {Product}
WHERE {code} = 'P001'Understanding Curly Braces
One of the most important things to remember is the use of:
{}For example:
{Product}represents an item type.
And:
{code}represents an attribute.
So:
SELECT {code}
FROM {Product}means:
Select the code attribute from Product items.
Selecting Multiple Attributes
You can select multiple attributes:
SELECT {pk}, {code}, {name}
FROM {Product}However, when working in Java, selecting only the data you actually need is often preferable to retrieving unnecessary columns.
Using Aliases
Aliases become extremely useful when working with joins.
Example:
SELECT {p.code}
FROM {Product AS p}Now p represents Product.
You can write:
SELECT {p.code}, {p.name}
FROM {Product AS p}
WHERE {p.code} = 'P001'WHERE Clause
The WHERE clause filters results.
Example:
SELECT {pk}, {code}
FROM {Product}
WHERE {code} = 'P001'You can also use:
ANDExample:
SELECT {pk}
FROM {Product}
WHERE {code} = 'P001'
AND {approvalStatus} IS NOT NULLOR Condition
Example:
SELECT {pk}, {code}
FROM {Product}
WHERE {code} = 'P001'
OR {code} = 'P002'You can also use:
INwhich is usually cleaner:
SELECT {pk}, {code}
FROM {Product}
WHERE {code} IN ('P001', 'P002', 'P003')LIKE Operator
FlexibleSearch supports pattern matching.
Example:
SELECT {pk}, {code}
FROM {Product}
WHERE {code} LIKE 'ABC%'This can find products whose code starts with:
ABCAnother example:
WHERE {name} LIKE '%Laptop%'NOT Operator
Example:
SELECT {pk}, {code}
FROM {Product}
WHERE {code} NOT LIKE 'TEST%'ORDER BY
You can sort results:
SELECT {pk}, {code}, {name}
FROM {Product}
ORDER BY {code}Descending:
ORDER BY {code} DESCMultiple sorting fields:
ORDER BY {name} ASC, {code} ASCParameterized FlexibleSearch
One of the most important best practices is to use parameters.
Avoid:
String query =
"SELECT {pk} FROM {Product} " +
"WHERE {code} = '" + productCode + "'";Instead:
String query =
"SELECT {pk} FROM {Product} " +
"WHERE {code} = ?code";Then:
FlexibleSearchQuery flexibleSearchQuery =
new FlexibleSearchQuery(query);
flexibleSearchQuery.addQueryParameter(
"code",
productCode);Finally:
SearchResult<ProductModel> result =
flexibleSearchService.search(
flexibleSearchQuery);SAP's API documentation describes FlexibleSearch execution through a FlexibleSearchQuery and the FlexibleSearchService. (SAP Help Portal)
Why Should You Use Parameters?
Parameterized queries provide several advantages:
Cleaner code
Better separation of query and data
Avoid string concatenation
Safer handling of values
Easier query maintenance
Therefore, prefer:
WHERE {code} = ?codeover dynamically concatenating values into the query string.
FlexibleSearchService
In Java, FlexibleSearch is commonly executed through:
FlexibleSearchServiceExample:
@Resource
private FlexibleSearchService flexibleSearchService;Then:
final String query =
"SELECT {pk} " +
"FROM {Product} " +
"WHERE {code} = ?code";
final FlexibleSearchQuery searchQuery =
new FlexibleSearchQuery(query);
searchQuery.addQueryParameter(
"code",
"P001");
SearchResult<ProductModel> result =
flexibleSearchService.search(searchQuery);Get the result:
List<ProductModel> products =
result.getResult();Complete DAO Example
A typical DAO method might look like:
public ProductModel findProductByCode(
final String code)
{
final String query =
"SELECT {p:pk} " +
"FROM {Product AS p} " +
"WHERE {p:code} = ?code";
final FlexibleSearchQuery searchQuery =
new FlexibleSearchQuery(query);
searchQuery.addQueryParameter(
"code",
code);
final SearchResult<ProductModel> result =
flexibleSearchService.search(searchQuery);
return result.getResult()
.stream()
.findFirst()
.orElse(null);
}This is a typical pattern for a SAP Commerce DAO.
FlexibleSearch Joins
Joins are extremely important in real SAP Commerce projects.
For example:
Order
|
└── UserWe can retrieve orders along with customer information.
Example:
SELECT
{o.code},
{u.uid}
FROM
{
Order AS o
JOIN User AS u
ON {o.user} = {u.pk}
}Product and CatalogVersion Join
A common SAP Commerce query involves products and catalog versions.
SELECT
{p.code},
{cv.version}
FROM
{
Product AS p
JOIN CatalogVersion AS cv
ON {p.catalogVersion} = {cv.pk}
}You can then filter:
WHERE {cv.version} = 'Online'Product and Category
Relations can also be queried.
Conceptually:
Product
|
| supercategories
|
CategoryA query can join the appropriate relation endpoint or related item attributes depending on the specific data model.
Always check the relevant Type System definitions before constructing a complex relation query.
FlexibleSearch with Enum
SAP Commerce frequently uses enumeration values.
For example:
OrderStatusmight contain:
CREATED
PROCESSING
COMPLETED
CANCELLEDDepending on the data model, you can query the corresponding enum value through the appropriate relation/reference.
For example:
SELECT {o.pk}
FROM {Order AS o}
WHERE {o.status} = ?statusIn Java:
searchQuery.addQueryParameter(
"status",
OrderStatus.CREATED);Using the model/enum value as a query parameter is generally preferable to hard-coding database-specific identifiers.
Date Queries
Date filtering is very common in CronJobs and reporting.
Example:
SELECT {pk}, {code}, {creationtime}
FROM {Order}
WHERE {creationtime} >= ?startDate
AND {creationtime} <= ?endDateJava:
searchQuery.addQueryParameter(
"startDate",
startDate);
searchQuery.addQueryParameter(
"endDate",
endDate);This is much better than constructing dates directly into the query string.
NULL Checks
To find products where an attribute is not populated:
SELECT {pk}, {code}
FROM {Product}
WHERE {description} IS NULLTo find populated values:
SELECT {pk}, {code}
FROM {Product}
WHERE {description} IS NOT NULLCOUNT
FlexibleSearch supports aggregate queries.
For example:
SELECT COUNT({pk})
FROM {Product}This can be useful when you only need a count instead of loading every model.
For large datasets, this can be significantly more appropriate than retrieving thousands of models just to call:
list.size()GROUP BY
Example:
SELECT
{catalogVersion},
COUNT({pk})
FROM {Product}
GROUP BY {catalogVersion}This can be useful for reporting and analysis.
Subqueries
FlexibleSearch also supports subqueries for applicable scenarios.
A conceptual example is:
SELECT {p.pk}
FROM {Product AS p}
WHERE {p.pk} IN
(
{{
SELECT {oe.product}
FROM {OrderEntry AS oe}
}}
)Subqueries should be used carefully, particularly for large datasets.
Always verify the generated SQL and execution performance for production workloads.
Searching Only a Specific Type
SAP Commerce supports type inheritance.
For example:
Product
|
└── VariantProductDepending on the query, searching the parent type can include subtypes.
The exact-type modifier can be useful when you specifically want to exclude subtypes.
Example:
SELECT {pk}
FROM {Product!}The ! is an important FlexibleSearch feature to understand when dealing with type inheritance.
Localized Attributes
SAP Commerce supports localized attributes.
For example, a product name may exist in:
English
German
FrenchFlexibleSearch can access localized values using the appropriate language information.
For example:
SELECT
{code},
{name[en]}
FROM {Product}The exact language handling depends on the platform language configuration and query requirements.
Pagination
Pagination is extremely important when retrieving large amounts of data.
A basic FlexibleSearchQuery can be configured with:
searchQuery.setStart(0);
searchQuery.setCount(100);
searchQuery.setNeedTotal(true);Conceptually:
Start = 0
Count = 100means:
Start from the first result and retrieve up to 100 results.
SAP Commerce also provides PaginatedFlexibleSearchService for pagination and sorting use cases. SAP's documentation describes it as supporting pagination and sorting through SearchPageData. (SAP Help Portal)
PaginatedFlexibleSearchService
For storefront and service-layer pagination scenarios, you may encounter:
PaginatedFlexibleSearchServiceIt works with objects such as:
SearchPageData
PageableData
SortDataA simplified flow is:
Request
↓
PageableData
↓
PaginatedFlexibleSearchService
↓
FlexibleSearch
↓
SearchPageDataThis is especially useful when exposing paginated results through a facade/controller layer.
FlexibleSearch in HAC
FlexibleSearch queries can also be executed through the SAP Commerce Administration Console.
For example:
SELECT {pk}, {code}
FROM {Product}This is extremely useful for:
Troubleshooting
Data validation
Production support
Development
Checking relationships
Investigating unexpected data
However, always be careful when running expensive queries against production databases.
FlexibleSearch Performance
Writing a query that works is not enough.
The query also needs to perform well.
For example, avoid:
for (ProductModel product : products)
{
String query =
"SELECT {pk} FROM {OrderEntry} " +
"WHERE {product} = ?product";
// Execute query
}This can result in the classic:
N+1 query problem
If there are 10,000 products, you could potentially execute thousands of database queries.
A better approach is often to retrieve the required information using one appropriately designed query or a controlled batching strategy.
Avoid FlexibleSearch Inside Loops
This is one of the most important SAP Commerce performance rules.
Bad:
for (ProductModel product : products)
{
getOrdersForProduct(product);
}where:
getOrdersForProduct()executes FlexibleSearch every time.
Better:
Design a query that retrieves the required data in a single operation, or process data in controlled batches.
Select Only What You Need
Avoid unnecessarily retrieving huge datasets.
Instead of:
SELECT {pk}, {code}, {name}, {description},
{manufacturerName}, {summary}, ...
FROM {Product}if you only need:
codeuse:
SELECT {code}
FROM {Product}For large datasets, this can reduce memory and processing overhead.
Read-Only Replica
SAP Commerce Cloud can support FlexibleSearch against a configured read-only data source.
SAP documentation notes that complex queries can be directed to a read-only replica to improve performance of the main data source. (SAP Help Portal)
This is particularly interesting for:
Reporting
Complex read queries
Large data analysis
Read-heavy workloads
However, read-only replicas have their own consistency considerations, so they should be used according to the application's requirements.
FlexibleSearch Restrictions
SAP Commerce can apply search restrictions to FlexibleSearch operations depending on the context.
This is another reason FlexibleSearch should not simply be treated as direct SQL.
The platform's security and search restriction mechanisms can influence the results returned.
FlexibleSearch vs ModelService
This is a very common interview question.
FlexibleSearch
Used primarily to:
READ / RETRIEVEdata.
ModelService
Used for:
CREATE
UPDATE
SAVE
REMOVE
REFRESHmodels.
Example:
List<ProductModel> products =
flexibleSearchService.search(query)
.getResult();Then:
product.setName("Updated Name");
modelService.save(product);So the typical flow is:
FlexibleSearch
↓
Retrieve Model
↓
Modify Model
↓
ModelService.save()FlexibleSearch vs Direct SQL
A common question is:
Why don't we simply use SQL?
Because SAP Commerce applications are built around the platform's Type System and persistence abstractions.
FlexibleSearch allows developers to query SAP Commerce types and attributes rather than coupling application code directly to database-specific table structures. SAP explicitly describes this Type System-oriented approach in its FlexibleSearch documentation. (SAP Help Portal)
Direct database access should therefore not be the default approach for normal application development.
Common FlexibleSearch Mistakes
1. Forgetting Braces
Wrong:
SELECT pk FROM ProductTypical FlexibleSearch syntax:
SELECT {pk}
FROM {Product}2. Hard-Coding Values
Avoid:
"WHERE {code} = '" + code + "'"Prefer:
"WHERE {code} = ?code"3. Selecting Too Much Data
Don't retrieve thousands of models when you only need a count.
Use:
SELECT COUNT({pk})
FROM {Product}4. FlexibleSearch Inside Loops
This can create severe performance problems.
Always look for opportunities to combine queries or batch processing.
5. Missing CatalogVersion
When working with catalog-aware types such as Product, remember that the same product code can exist in different catalog versions depending on the data model.
For example:
Product
|
+-- Staged
|
+-- OnlineA query may therefore need to explicitly consider catalog version.
Real-World Example: Find Products in Online Catalog
SELECT
{p.code},
{p.name}
FROM
{
Product AS p
JOIN CatalogVersion AS cv
ON {p.catalogVersion} = {cv.pk}
JOIN Catalog AS c
ON {cv.catalog} = {c.pk}
}
WHERE {cv.version} = 'Online'You could further restrict the catalog:
AND {c.id} = 'electronicsProductCatalog'This is a very common type of query in real SAP Commerce projects.
Real-World Example: Find Orders Created Today
A parameterized query is preferable:
SELECT
{o.pk},
{o.code},
{o.creationtime}
FROM {Order AS o}
WHERE {o.creationtime} >= ?startDate
AND {o.creationtime} < ?endDateJava:
query.addQueryParameter(
"startDate",
startDate);
query.addQueryParameter(
"endDate",
endDate);Using a half-open interval:
>= start
< endcan make date-range handling cleaner and avoid boundary problems.
Real-World Example: Count Orders by User
SELECT
{o.user},
COUNT({o.pk})
FROM {Order AS o}
GROUP BY {o.user}This can be useful for reporting or analysis.
Debugging a Slow FlexibleSearch Query
When a FlexibleSearch query is slow, don't immediately assume FlexibleSearch itself is the problem.
Investigate:
Query complexity
Number of joins
Search restrictions
Database indexes
Number of records
Sorting
Pagination
Execution plan
Large result sets
N+1 query patterns
A query that works perfectly with 1,000 products may behave very differently with millions of records.
FlexibleSearch Best Practices
✅ Use Query Parameters
WHERE {code} = ?code✅ Retrieve Only Required Data
Don't select unnecessary attributes.
✅ Use Pagination
Especially for large result sets.
✅ Avoid Queries Inside Loops
Look for batch or join-based solutions.
✅ Filter Early
Apply restrictive conditions wherever appropriate.
✅ Understand the Type System
Know the relationships between:
Product
Catalog
CatalogVersion
Category
Order
OrderEntry
Customer✅ Test Production-Scale Data
A query that works locally is not necessarily production-ready.
Interview Questions
What is FlexibleSearch?
FlexibleSearch is SAP Commerce's SQL-like query language used to retrieve data through the SAP Commerce Type System.
What is FlexibleSearchService?
FlexibleSearchService is the Service Layer API used to execute FlexibleSearch queries.
Why do we use {} in FlexibleSearch?
Curly braces identify SAP Commerce item types and attributes within FlexibleSearch syntax.
Example:
{Product}
{code}
{p.code}What is the difference between SQL and FlexibleSearch?
SQL operates directly on database tables and columns, while FlexibleSearch works primarily with SAP Commerce item types and attributes and is translated into database-specific SQL.
How do you pass parameters?
Use:
WHERE {code} = ?codeand:
query.addQueryParameter("code", code);How do you implement pagination?
You can configure FlexibleSearchQuery with start/count settings or use PaginatedFlexibleSearchService for pagination and sorting use cases. (SAP Help Portal)
How do you improve FlexibleSearch performance?
Common approaches include:
Avoiding N+1 queries
Avoiding FlexibleSearch inside loops
Selecting only required data
Using appropriate filtering
Pagination
Reviewing database indexes
Reducing unnecessary joins
Testing against realistic data volumes
Can FlexibleSearch update data?
FlexibleSearch is primarily used for retrieval. For normal application-level create/update/delete operations, SAP Commerce developers typically use APIs such as ModelService or Impex rather than treating FlexibleSearch as a general-purpose DML language.
Conclusion
FlexibleSearch is one of the most important technologies to master as an SAP Commerce developer.
A strong developer should be comfortable with:
SELECT
WHERE
AND / OR
LIKE
IN
ORDER BY
JOIN
COUNT
GROUP BY
Subqueries
Parameters
Pagination
Localized attributes
RelationsBut knowing the syntax is only half the job.
For real-world SAP Commerce development, you also need to understand performance.
The most important rule to remember is:
A FlexibleSearch query that returns the correct result is not necessarily a good production query.
Always consider:
Correctness
+
Performance
+
Scalability
+
MaintainabilityThat mindset will help you write much better SAP Commerce code.
