Every SAP Commerce developer writes FlexibleSearch queries daily. It’s how you retrieve products, look up orders, debug data issues in HAC, and build the DAOs that power your services. Yet many developers treat it as “just SQL with curly braces” and miss the nuances that separate a query that works from one that performs well at scale.
This guide covers FlexibleSearch from first principles to production-hardened patterns. Every concept is illustrated with a working query, and we dig into the internals — how the engine translates your query, how caching works, and where the performance traps hide.
FlexibleSearch is SAP Commerce’s query language for retrieving data from the type system. It looks like SQL but operates on the platform’s type model rather than raw database tables. When you write:
SELECT {pk} FROM {Product}
You’re not querying a products table directly. The FlexibleSearch engine (de.hybris.platform.jalo.flexiblesearch.FlexibleSearch) translates this into actual SQL by:
{Product} to the correct database table(s), including joined tables for subtypes (single-table inheritance or separate tables){pk} to the actual primary key column nameSearchResultThe translation layer lives in de.hybris.platform.persistence.flexiblesearch.TranslatedQuery. You can see the generated SQL in HAC’s FlexibleSearch console — after running a query, the actual SQL appears below the results.
| Feature | FlexibleSearch | Standard SQL |
|---|---|---|
| Target | Type system types | Database tables |
| Syntax | {attribute} curly braces |
Column names directly |
| Localization | Built-in {name[en]} |
Manual join to localization tables |
| Type hierarchy | Automatic subtype inclusion | Manual UNION or JOIN |
| Catalog filtering | Session-based automatic | Manual WHERE clauses |
| Parameterization | ?param syntax |
? or :param |
| Functions | Limited (DB-dependent) | Full SQL function support |
| INSERT/UPDATE/DELETE | Not supported | Supported |
Key limitation: FlexibleSearch is read-only. You cannot use it to modify data. All writes go through ModelService.
SELECT {pk} FROM {Product}
Returns the PKs of all Product items (including subtypes like VariantProduct, ApparelProduct, etc.). FlexibleSearch always requires at least {pk} in the SELECT clause.
SELECT {pk}, {code}, {name} FROM {Product}
Returns PKs, codes, and names. Note: when you use FlexibleSearchService in Java code, the result always returns Model objects hydrated via PK — the additional SELECT columns are mainly useful for HAC debugging or when using raw result mode.
SELECT {pk} FROM {Product} WHERE {code} = 'PROD-001'
SELECT {pk} FROM {Product} WHERE {approvalStatus} = {{SELECT {pk} FROM {ArticleApprovalStatus} WHERE {code} = 'approved'}}
SELECT {pk} FROM {Product} WHERE {name} IS NOT NULL ORDER BY {name} ASC
FlexibleSearch doesn’t have a LIMIT keyword. Pagination is controlled via the Java API:
FlexibleSearchQuery query = new FlexibleSearchQuery("SELECT {pk} FROM {Product}");
query.setStart(0); // offset
query.setCount(20); // page size
SearchResult<ProductModel> result = flexibleSearchService.search(query);
SELECT DISTINCT {p.code} FROM {Product AS p}
SELECT COUNT({pk}) FROM {Product} WHERE {approvalStatus} = {{SELECT {pk} FROM {ArticleApprovalStatus} WHERE {code} = 'approved'}}
When using COUNT in Java:
FlexibleSearchQuery query = new FlexibleSearchQuery(
"SELECT COUNT({pk}) FROM {Product}");
query.setResultClassList(Arrays.asList(Integer.class));
SearchResult<Integer> result = flexibleSearchService.search(query);
int totalProducts = result.getResult().get(0);
The curly braces are what make FlexibleSearch different from SQL. They reference the type system, not database columns.
{attribute} — Simple Attribute{code} -- resolves to the 'code' column of the item's table
{name} -- resolves to the 'name' column (default language)
{pk} -- the item's primary key
{creationtime} -- platform-level audit attribute
{modifiedtime} -- platform-level audit attribute
{name[en]} — Localized AttributeLocalized attributes are stored in separate *lp tables. The [lang] syntax generates the necessary JOIN:
SELECT {pk} FROM {Product} WHERE {name[en]} LIKE '%widget%'
This translates to a JOIN with the localization table, filtering by the en language code.
You can query multiple languages:
SELECT {pk}, {name[en]}, {name[de]}, {name[fr]} FROM {Product}
{alias.attribute} — Aliased ReferencesWhen joining types, use aliases to disambiguate:
SELECT {p.pk}, {p.code}, {c.code}
FROM {Product AS p
JOIN Category AS c ON {p.supercategories} = {c.pk}}
{Type} — Type Reference in FROM ClauseFROM {Product} -- includes all subtypes
FROM {Product!} -- EXCLUDES subtypes (exact type only)
FROM {Product AS p} -- with alias
FROM {Product*} -- same as {Product}, includes subtypes (explicit)
The ! suffix is crucial. {Product} returns Products, VariantProducts, ApparelProducts, etc. {Product!} returns only items whose exact type is Product.
{{subquery}} — SubqueriesDouble curly braces denote subqueries:
SELECT {pk} FROM {Product}
WHERE {catalogVersion} IN (
{{SELECT {pk} FROM {CatalogVersion}
WHERE {catalog} IN (
{{SELECT {pk} FROM {Catalog} WHERE {id} = 'myProductCatalog'}}
) AND {version} = 'Online'}}
)
FlexibleSearch supports JOIN on type relations:
SELECT {p.pk}, {p.code}, {cv.version}
FROM {Product AS p
JOIN CatalogVersion AS cv ON {p.catalogVersion} = {cv.pk}
JOIN Catalog AS cat ON {cv.catalog} = {cat.pk}}
WHERE {cat.id} = 'myProductCatalog'
AND {cv.version} = 'Online'
SELECT {p.pk}, {p.code}, {s.available}
FROM {Product AS p
LEFT JOIN StockLevel AS s ON {s.productCode} = {p.code}}
WHERE {p.catalogVersion} = ?catalogVersion
Many-to-many relations in SAP Commerce create link tables. You reference them by the relation name:
-- Products in a specific category (many-to-many via CategoryProductRelation)
SELECT {p.pk}
FROM {Product AS p
JOIN CategoryProductRelation AS rel ON {rel.target} = {p.pk}
JOIN Category AS c ON {rel.source} = {c.pk}}
WHERE {c.code} = 'electronics'
AND {p.catalogVersion} = ?catalogVersion
Alternatively, use the implicit relation traversal:
SELECT {p.pk}
FROM {Category AS c
JOIN CategoryProductRelation AS cpr ON {cpr.source} = {c.pk}
JOIN Product AS p ON {cpr.target} = {p.pk}}
WHERE {c.code} = 'electronics'
This is the most common join pattern in SAP Commerce:
SELECT {p.pk}
FROM {Product AS p
JOIN CatalogVersion AS cv ON {p.catalogVersion} = {cv.pk}
JOIN Catalog AS cat ON {cv.catalog} = {cat.pk}}
WHERE {cat.id} = 'electronicsProductCatalog'
AND {cv.version} = 'Staged'
AND {p.approvalStatus} = {{SELECT {pk} FROM {ArticleApprovalStatus} WHERE {code} = 'approved'}}
ORDER BY {p.code}
Pro tip: In most application code, you don’t need explicit catalog version JOINs. The platform applies catalog version filtering from the session context automatically. But in HAC and unit tests, you must be explicit.
Always use parameterized queries to prevent SQL injection and enable query plan caching.
?parameter SyntaxSELECT {pk} FROM {Product}
WHERE {code} = ?code
AND {catalogVersion} = ?catalogVersion
In Java:
FlexibleSearchQuery query = new FlexibleSearchQuery(
"SELECT {pk} FROM {Product} WHERE {code} = ?code AND {catalogVersion} = ?catalogVersion");
query.addQueryParameter("code", "PROD-001");
query.addQueryParameter("catalogVersion", catalogVersionModel);
SearchResult<ProductModel> result = flexibleSearchService.search(query);
You can pass Model objects directly — the engine extracts the PK:
query.addQueryParameter("catalogVersion", catalogVersionService.getCatalogVersion("myProductCatalog", "Staged"));
query.addQueryParameter("category", categoryModel);
query.addQueryParameter("user", userService.getCurrentUser());
For IN clauses, pass a Collection:
List<String> codes = Arrays.asList("PROD-001", "PROD-002", "PROD-003");
FlexibleSearchQuery query = new FlexibleSearchQuery(
"SELECT {pk} FROM {Product} WHERE {code} IN (?codes)");
query.addQueryParameter("codes", codes);
query.addQueryParameter("status", ArticleApprovalStatus.APPROVED);
Querying localized data has specific patterns in FlexibleSearch.
SELECT {pk}, {name} FROM {Product} WHERE {name} LIKE '%widget%'
Without a language qualifier, {name} uses the current session language.
SELECT {pk}, {name[en]}, {name[de]} FROM {Product} WHERE {name[en]} IS NOT NULL
SELECT {pk}, {code}, {name[en]}, {name[de]}
FROM {Product}
WHERE {name[en]} IS NOT NULL
AND {name[de]} IS NULL
AND {catalogVersion} = ?catalogVersion
This is valuable for translation quality assurance.
To find a product regardless of which language the name was entered in:
SELECT {pk} FROM {Product}
WHERE {name[en]} LIKE '%search%'
OR {name[de]} LIKE '%search%'
OR {name[fr]} LIKE '%search%'
Not elegant, but necessary when you need cross-language search without Solr.
SELECT {p.pk}
FROM {Product AS p}
WHERE {p.catalogVersion} = ?catalogVersion
AND {p.approvalStatus} = ?approvalStatus
AND {p.code} LIKE ?codePattern
ORDER BY {p.code} ASC
query.addQueryParameter("catalogVersion", onlineCatalogVersion);
query.addQueryParameter("approvalStatus", ArticleApprovalStatus.APPROVED);
query.addQueryParameter("codePattern", "ELEC-%");
SELECT {o.pk}
FROM {Order AS o
JOIN User AS u ON {o.user} = {u.pk}}
WHERE {u.uid} = ?userId
ORDER BY {o.creationtime} DESC
SELECT {pk}
FROM {Order}
WHERE {creationtime} >= ?startDate
AND {creationtime} < ?endDate
AND {status} = {{SELECT {pk} FROM {OrderStatus} WHERE {code} = 'COMPLETED'}}
ORDER BY {creationtime} DESC
-- Find customers by email domain
SELECT {pk}, {uid}, {name}
FROM {Customer}
WHERE {uid} LIKE '%@example.com'
-- Find customers who placed orders in the last 30 days
SELECT DISTINCT {u.pk}
FROM {Order AS o
JOIN Customer AS u ON {o.user} = {u.pk}}
WHERE {o.creationtime} > ?thirtyDaysAgo
SELECT {pk}
FROM {CMSParagraphComponent}
WHERE {catalogVersion} = ?contentCatalogVersion
AND {uid} = ?componentUid
SELECT {pr.pk}, {pr.price}, {pr.currency}, {pr.net}
FROM {PriceRow AS pr
JOIN Product AS p ON {pr.product} = {p.pk}}
WHERE {p.code} = ?productCode
AND {pr.catalogVersion} = ?catalogVersion
AND ({pr.startTime} IS NULL OR {pr.startTime} <= ?now)
AND ({pr.endTime} IS NULL OR {pr.endTime} > ?now)
SELECT {sl.pk}, {sl.available}, {sl.warehouse}
FROM {StockLevel AS sl}
WHERE {sl.productCode} = ?productCode
AND {sl.warehouse} IN (?warehouses)
Never use SELECT * in FlexibleSearch. Always select specific attributes, or at minimum just {pk}:
-- BAD: triggers full row hydration for every column
SELECT * FROM {Product}
-- GOOD: returns PKs, models are lazy-loaded on access
SELECT {pk} FROM {Product}
When FlexibleSearchService returns SearchResult<ProductModel>, the Model objects are populated lazily — attributes are fetched from cache or database only when accessed. Selecting {pk} is sufficient.
SAP Commerce creates indexes from items.xml definitions:
<itemtype code="LoyaltyTransaction" ...>
<indexes>
<index name="codeIdx" unique="true">
<key attribute="code"/>
</index>
<index name="customerDateIdx">
<key attribute="customer"/>
<key attribute="transactionDate"/>
</index>
</indexes>
</itemtype>
Design indexes based on your query patterns. If you frequently query WHERE {customer} = ? AND {transactionDate} > ?, create a composite index on both attributes.
FlexibleSearch supports result caching. Enable it per query:
FlexibleSearchQuery query = new FlexibleSearchQuery("SELECT {pk} FROM {Currency}");
query.setCacheable(true);
Or enable it globally for specific type queries:
# Cache all FlexibleSearch results for Currency type for 300 seconds
flexiblesearch.cache.enabled=true
Caution: Caching mutable data (products, orders) requires careful invalidation. Best used for slowly-changing reference data (currencies, languages, units, countries).
In HAC’s FlexibleSearch console, enable “Show SQL” to see the generated SQL. Copy that SQL and run EXPLAIN in your database console:
-- For HANA
EXPLAIN PLAN FOR SELECT ... FROM products p0 WHERE ...
-- For MySQL
EXPLAIN SELECT ... FROM products p0 WHERE ...
Look for:
A common anti-pattern in DAOs:
// BAD: N+1 queries
List<ProductModel> products = getProducts(catalogVersion);
for (ProductModel product : products) {
List<PriceRowModel> prices = getPricesForProduct(product); // Another query per product!
}
Better approach — use a single JOIN query:
// GOOD: Single query with JOIN
String queryStr = "SELECT {p.pk} FROM {Product AS p " +
"JOIN PriceRow AS pr ON {pr.product} = {p.pk}} " +
"WHERE {p.catalogVersion} = ?cv AND {pr.price} < ?maxPrice";
Or prefetch prices in batch:
String queryStr = "SELECT {pk} FROM {PriceRow} WHERE {product} IN (?products)";
query.addQueryParameter("products", productModels);
Both are query mechanisms, but they serve different purposes.
| Aspect | FlexibleSearch | Solr |
|---|---|---|
| Source | Database (live data) | Solr index (snapshot) |
| Speed | Slower for full-text | Faster for full-text |
| Data freshness | Real-time | Eventual (after indexing) |
| Full-text search | Basic (LIKE) | Advanced (stemming, synonyms, fuzzy) |
| Faceting | Not built-in | Built-in |
| Use case | Service layer DAOs, admin, reports | Storefront product search |
| Scalability | Limited by DB | Horizontally scalable |
Use FlexibleSearch when:
Use Solr when:
In the standard architecture, CommerceSearchService (which delegates to Solr) handles storefront product search, while FlexibleSearchService handles everything else.
The primary Java API:
@Resource
private FlexibleSearchService flexibleSearchService;
public List<ProductModel> findProductsByCategory(CategoryModel category, CatalogVersionModel cv) {
String queryStr = "SELECT {p.pk} FROM {Product AS p " +
"JOIN CategoryProductRelation AS cpr ON {cpr.target} = {p.pk} " +
"JOIN Category AS c ON {cpr.source} = {c.pk}} " +
"WHERE {c.pk} = ?category AND {p.catalogVersion} = ?cv " +
"ORDER BY {p.name} ASC";
FlexibleSearchQuery query = new FlexibleSearchQuery(queryStr);
query.addQueryParameter("category", category);
query.addQueryParameter("cv", cv);
query.setCount(100); // max results
SearchResult<ProductModel> result = flexibleSearchService.search(query);
return result.getResult();
}
SearchResult<ProductModel> result = flexibleSearchService.search(query);
List<ProductModel> items = result.getResult(); // The actual results
int totalCount = result.getTotalCount(); // Total matching items (for pagination)
int requestedCount = result.getRequestedCount(); // The count you requested
int requestedStart = result.getRequestedStart(); // The offset you requested
public SearchPageData<ProductModel> findProducts(int page, int pageSize) {
FlexibleSearchQuery query = new FlexibleSearchQuery("SELECT {pk} FROM {Product}");
query.setStart(page * pageSize);
query.setCount(pageSize);
query.setNeedTotal(true); // Required for total count
SearchResult<ProductModel> result = flexibleSearchService.search(query);
SearchPageData<ProductModel> pageData = new SearchPageData<>();
pageData.setResults(result.getResult());
PaginationData pagination = new PaginationData();
pagination.setTotalNumberOfResults(result.getTotalCount());
pagination.setCurrentPage(page);
pagination.setPageSize(pageSize);
pagination.setNumberOfPages((int) Math.ceil((double) result.getTotalCount() / pageSize));
pageData.setPagination(pagination);
return pageData;
}
When you need non-Model results (aggregations, projections):
FlexibleSearchQuery query = new FlexibleSearchQuery(
"SELECT {approvalStatus}, COUNT({pk}) FROM {Product} GROUP BY {approvalStatus}");
query.setResultClassList(Arrays.asList(Object.class, Integer.class));
SearchResult<List<Object>> result = flexibleSearchService.search(query);
for (List<Object> row : result.getResult()) {
Object status = row.get(0);
Integer count = (Integer) row.get(1);
// process...
}
The Hybris Administration Console (HAC) is your primary tool for ad-hoc FlexibleSearch queries.
Navigate to https://localhost:9002/hac/console/flexsearch (or your HAC URL). You’ll see:
Check if an item exists:
SELECT {pk}, {code}, {name[en]}, {catalogVersion} FROM {Product} WHERE {code} = 'PROD-001'
Inspect all attributes of an item:
SELECT * FROM {Product} WHERE {code} = 'PROD-001'
Check catalog versions:
SELECT {cv.pk}, {cv.version}, {cat.id}
FROM {CatalogVersion AS cv JOIN Catalog AS cat ON {cv.catalog} = {cat.pk}}
Find orphaned items:
-- Products without categories
SELECT {p.pk}, {p.code}
FROM {Product AS p}
WHERE {p.catalogVersion} = ?cv
AND NOT EXISTS (
{{SELECT 1 FROM {CategoryProductRelation AS cpr} WHERE {cpr.target} = {p.pk}}}
)
Check recent modifications:
SELECT {pk}, {code}, {modifiedtime}
FROM {Product}
WHERE {modifiedtime} > '2025-03-01 00:00:00'
ORDER BY {modifiedtime} DESC
HAC doesn’t automatically set a catalog version session context. For queries that rely on it, use explicit WHERE clauses:
-- Explicit catalog version
SELECT {pk} FROM {Product}
WHERE {catalogVersion} IN (
{{SELECT {pk} FROM {CatalogVersion}
WHERE {version} = 'Online'
AND {catalog} IN ({{SELECT {pk} FROM {Catalog} WHERE {id} = 'myProductCatalog'}})}})
SELECT {p.pk}, {p.code}, {p.name[en]}
FROM {Product AS p}
WHERE {p.catalogVersion} = ?catalogVersion
AND {p.approvalStatus} = {{SELECT {pk} FROM {ArticleApprovalStatus} WHERE {code} = 'approved'}}
AND NOT EXISTS (
{{SELECT {pr.pk} FROM {PriceRow AS pr}
WHERE {pr.product} = {p.pk}
AND {pr.currency} = {{SELECT {pk} FROM {Currency} WHERE {isocode} = 'USD'}}
}}
)
ORDER BY {p.code}
SELECT {u.uid}, {u.name}, COUNT({o.pk}) AS orderCount
FROM {Order AS o
JOIN Customer AS u ON {o.user} = {u.pk}}
WHERE {o.creationtime} > ?ninetyDaysAgo
GROUP BY {u.uid}, {u.name}
ORDER BY orderCount DESC
SELECT {p.code}, {p.name[en]}, SUM({sl.available}) AS totalStock
FROM {StockLevel AS sl
JOIN Product AS p ON {sl.productCode} = {p.code}}
WHERE {p.catalogVersion} = ?catalogVersion
GROUP BY {p.code}, {p.name[en]}
HAVING SUM({sl.available}) < 10
ORDER BY totalStock ASC
SELECT {staged.pk}, {staged.code}
FROM {Product AS staged}
WHERE {staged.catalogVersion} = ?stagedCV
AND NOT EXISTS (
{{SELECT {online.pk} FROM {Product AS online}
WHERE {online.code} = {staged.code}
AND {online.catalogVersion} = ?onlineCV}}
)
SELECT {page.pk}, {page.uid}, {page.name}
FROM {ContentPage AS page}
WHERE {page.catalogVersion} = ?contentCatalogVersion
AND EXISTS (
{{SELECT {rel.pk}
FROM {ContentSlotForPage AS rel}
WHERE {rel.page} = {page.pk}
AND NOT EXISTS (
{{SELECT {crel.pk}
FROM {ElementsForSlot AS crel}
WHERE {crel.source} IN (
{{SELECT {slot.pk} FROM {ContentSlot AS slot}
WHERE {slot.pk} = {rel.contentSlot}}}
)}}
)}}
)
SELECT MONTH({o.creationtime}), YEAR({o.creationtime}),
SUM({o.totalPrice}), {o.currency}
FROM {Order AS o}
WHERE {o.creationtime} >= ?startDate
AND {o.status} = {{SELECT {pk} FROM {OrderStatus} WHERE {code} = 'COMPLETED'}}
GROUP BY MONTH({o.creationtime}), YEAR({o.creationtime}), {o.currency}
ORDER BY YEAR({o.creationtime}), MONTH({o.creationtime})
Note: Date functions like MONTH() and YEAR() are database-specific. These work on HANA and MySQL but may need adjustment for other databases.
Problem: Query returns zero results even though the data exists.
-- Returns nothing because no catalog version context is set
SELECT {pk} FROM {Product} WHERE {code} = 'PROD-001'
Fix: Always include catalog version in the WHERE clause or set it in the session:
SELECT {pk} FROM {Product} WHERE {code} = 'PROD-001' AND {catalogVersion} = ?cv
Problem: Selects all columns, including LOB fields, causing unnecessary data transfer.
Fix: Always SELECT {pk} and let the Model layer handle lazy loading.
Problem: String concatenation opens SQL injection vectors and prevents query plan caching.
// BAD
String query = "SELECT {pk} FROM {Product} WHERE {code} = '" + userInput + "'";
// GOOD
FlexibleSearchQuery query = new FlexibleSearchQuery(
"SELECT {pk} FROM {Product} WHERE {code} = ?code");
query.addQueryParameter("code", userInput);
! Suffix for Exact Type MatchingProblem: You want only base Products but also get VariantProducts, ApparelProducts, etc.
-- Returns ALL product subtypes
SELECT {pk} FROM {Product}
-- Returns ONLY exact Product type
SELECT {pk} FROM {Product!}
setNeedTotal(true) for PaginationProblem: getTotalCount() returns -1.
query.setNeedTotal(true); // Must be set BEFORE search
SearchResult result = flexibleSearchService.search(query);
result.getTotalCount(); // Now returns the actual count
Problem: Executing a query per item in a loop.
Fix: Batch your queries. Use IN clauses with collections or JOINs.
Problem: LIKE '%searchterm%' cannot use indexes and triggers full table scans.
-- Slow: leading wildcard prevents index usage
WHERE {name[en]} LIKE '%widget%'
-- Faster: trailing wildcard only
WHERE {name[en]} LIKE 'widget%'
For full-text search, use Solr instead.
Problem: Inconsistent results depending on session language.
Fix: Always specify the language explicitly when the query is language-specific:
WHERE {name[en]} LIKE '%widget%' -- explicit
-- vs
WHERE {name} LIKE '%widget%' -- depends on session language
SearchResult<ProductModel> result = flexibleSearchService.search(query);
List<ProductModel> products = result.getResult();
if (products.isEmpty()) {
// Handle no results — don't just .get(0)
}
Problem: Deeply nested subqueries can be very slow.
-- Slow: 3 levels of subquery nesting
WHERE {catalogVersion} IN ({{SELECT ... WHERE {catalog} IN ({{SELECT ... WHERE ...}})}})
Fix: Use JOINs instead of subqueries when possible:
-- Faster: JOIN-based approach
FROM {Product AS p
JOIN CatalogVersion AS cv ON {p.catalogVersion} = {cv.pk}
JOIN Catalog AS cat ON {cv.catalog} = {cat.pk}}
WHERE {cat.id} = 'myProductCatalog' AND {cv.version} = 'Online'
FlexibleSearch is the primary data query mechanism in SAP Commerce. The key principles:
?param syntax for safety and performanceFlexibleSearch is a tool you’ll use every day on SAP Commerce projects. Invest in understanding it deeply, and it will pay dividends in debugging speed, query performance, and code quality.