Order and limit results

AI-generated Key Takeaways

• The Merchant API is the new version of the Content API for Shopping and represents the future of product data integration.

• You can order results with the ORDER BY clause using fields and ASC or DESC for ascending or descending order respectively.

• The LIMIT clause enables you to specify the maximum number of returned results and can be combined with ORDER BY to generate "top N" reports.

• While a query's maximum result limit is 1000 rows, pagination with pageToken is necessary for retrieving additional results beyond this limit.

• Filtering using the WHERE clause on metrics necessitates their selection in the query.

You can specify the order of rows in your response using the ORDER BY clause, which consists of one or more comma-separated sequences of:

FieldName ('ASC' | 'DESC')?

If you don't specify ASC or DESC after a field name, the Reporting API defaults to ASC.

The following ORDER BY clause sorts a performance report by descending impressions and ascending product offer IDs:

ORDER BY metrics.impressions DESC, segments.offer_id ASC

ORDER BY is not allowed on non-selected metrics or non-selected segments.

Limit the number of results

You can limit the total number of results returned using the LIMIT clause. Combining this with results ordering, you can produce "top N" reports, such as a report containing the five products with the highest impressions over the last 30 days:

Example

SELECT
  segments.offer_id,
  metrics.impressions
FROM MerchantPerformanceView
WHERE segments.date BETWEEN '2020-12-01' AND '2020-12-31'
ORDER BY metrics.impressions DESC
LIMIT 5;

Note: The maximimum number of rows you can retrieve from a query is 1000, even if your limit is higher. You have to issue consecutive requests with the pageToken for the remaining results. To learn more, see our pagination guide.

Filtering (WHERE clause) is not allowed on metrics without selecting them.