Here are some examples of what can be query with FlexibleSearch. Please feel to send me complex queries which could help others understand the syntax.

• Get the cms components assigned to a content slot. It doesn't take the catalog version into account.

SELECT {e2s:target}
  FROM {
    contentslot AS s
  JOIN ElementsForSlot AS e2s
    ON {e2s:SOURCE} = {s:pk} }
  WHERE {s:uid} = 'priceChartEvolutionPage_main_slot'

• Get all fields of an object of type Player where the isocode of the country of the object equals to “SYR”. Player is a subtype of User

SELECT *
  FROM { Player AS p
      JOIN Country AS c
        ON {c:pk} = {p:country}
        AND {c:isocode}='SYR'
    }

• Get all the applied voucher and their orders of 2016. It may not work with relative vouchers

SELECT {v.code} voucherCode, {o.code} orderCode, {o.date} orderDate, {o.TotalDiscounts} orderTotalDiscounts,{d.value} discountValue
FROM {VoucherInvalidation AS vi 
   JOIN Voucher AS v
     ON {vi.voucher} = {v.pk}
   JOIN ORDER AS o
     ON {vi.order} = {o.pk}
     AND {o.plzForCalculation} IS NOT NULL
     AND {o.date} BETWEEN '2016-01-01' AND '2016-12-31 23:59:59'
   JOIN OrderDiscountRelation AS odr
     ON {odr.source} = {o.pk}
    JOIN Discount AS d
     ON {odr.target} = {d.pk} 
 }
 WHERE {vi.status} = 'confirmed'

• Modified point of services or their address

SELECT {p.pk }
FROM {PointOfService AS p
LEFT OUTER JOIN Address AS a
  ON {p.address} = {a.pk}}
WHERE {p.modifiedtime} >= '2020-10-01'
  OR {a.modifiedtime} >= '2020-10-01'
ORDER BY {p.baseStore}, {p.creationtime} DESC

• Get the pk of the warehouses which have at least one plzRange.

SELECT {ware.pk}
FROM {Warehouse AS ware}
  WHERE EXISTS ({{
        SELECT 1
        FROM {PLZRange AS plz}
        WHERE {ware:pk} = {plz:WAREHOUSE}
    }})

• Get the pk of the oldest CMSMedia which is duplicated:

SELECT {code}, {catalogversion}, MIN(pk)
FROM {CMSMedia}
GROUP BY {code}, {catalogversion}
HAVING COUNT(1) > 1

• Get the list of results adding a text. This is an useless example

SELECT x.description, x.text
FROM ({{
    SELECT {r:description[en]} description, 'End result' text
    FROM {ScriptExecutionResult AS r}
    WHERE {r:canBeRunnedAgain} = 0
}} UNION ALL {{
    SELECT {rr:description[en]}, 'Retry result' text
    FROM {ScriptExecutionResult AS rr}
    WHERE {rr:canBeRunnedAgain} = 1
}}) x

A select of the result of the union is mandatory.

• Using the square brackets you select value of a localized attribute for the given language

SELECT {t:code}, {t:subject[en]}, {t:subject[de]}
  FROM {RendererTemplate AS t}

• It looks for the deployment environments of the Areco Deployment Manager Extension

@Repository
public class FlexibleSearchDeploymentEnvironmentDAO implements DeploymentEnvironmentDAO { 
 
  (...)
 
 /**
   * {@inheritDoc }
   */
  @Override
  public Set<DeploymentEnvironmentModel> loadEnvironments(final Set<String> environmentNames) {
    (...)
 
    final Set<String> normalizedEnvironmentNames = new HashSet<String>();
    for (final String givenEnvironmentName : environmentNames) {
      normalizedEnvironmentNames.add(givenEnvironmentName.trim().toUpperCase(Locale.getDefault()));
    }
 
    final StringBuilder queryBuilder = new StringBuilder();
    queryBuilder.append("SELECT {r.").append(ItemModel.PK).append("}").append(" FROM {")
        .append(DeploymentEnvironmentModel._TYPECODE).append(" as r ").append("} ").append(" WHERE ").append(" UPPER({")
        .append(DeploymentEnvironmentModel.NAME).append("}) ").append(" IN ").append('(').append('?')
        .append(DeploymentEnvironmentModel.NAME).append(')');
 
    final FlexibleSearchQuery query = new FlexibleSearchQuery(queryBuilder.toString());
    query.addQueryParameter(DeploymentEnvironmentModel.NAME, normalizedEnvironmentNames);
    final SearchResult<ScriptExecutionResultModel> searchResult = this.flexibleSearchService.search(query);
    if (environmentNames.size() != searchResult.getCount()) {
      throw new IllegalStateException("Some environments don't exist. Please check that these names are valid: "
          + environmentNames);
    }
    return new HashSet(searchResult.getResult());
  }
 
  (...)
 
}

• Log into HAC
• Go to Console > Flexible Search
• Past your Flexible Search Code
• Run it
• Then go the the Tab “SQL Query” where you will find the raw SQL code
• The question marks must be replaced with the values of the Hybris' type and catalog versions PKs. You can see these values on the tab “Execution statistics”

Running a Flexible Search Query on HAC

How to see the raw SQL on the Flexible Search console on HAC

How to see what parameters where replaced on the query

– Based on Hybris 6.6