DEV Community

Cover image for You can't GROUP BY in DynamoDB — so the client does the counting
Ibukun Demehin
Ibukun Demehin

Posted on

You can't GROUP BY in DynamoDB — so the client does the counting

#aws #dynamodb #reactnative #buildinpublic

The feature request was almost embarrassingly simple: groceries are mostly repeat purchases, so show one-tap chips of the user's usual items — "Add again: Milk · Bread · Eggs." In SQL, that's a beginner exercise:

SELECT name, COUNT(*) AS times
FROM shopping_items
WHERE user_id = :me
GROUP BY name
ORDER BY times DESC
LIMIT 8;
Enter fullscreen mode Exit fullscreen mode

My database is DynamoDB, behind AppSync. There is no GROUP BY. No COUNT, no aggregates, no HAVING — the query model fetches items by key, full stop. So a one-line feature became an actual architecture decision, and the decision is more interesting than the feature.

TL;DR — NoSQL doesn't do read-time aggregation, so you either materialise at write-time (a counter table you upsert on every add) or aggregate at read-time on the client (fetch history, count in JS). For a personal-scale dataset, ship the client scan — but give it explicit budgets (page size, page cap, cache TTL), make the counts honest (soft-deleted rows in, template lists out), and write down the exit ramp to the materialised model before you need it.

(Part 10 of Building CannyCart, a voice-first shopping app I'm building in public. Self-contained — no earlier context needed.)

The fork: count on write, or count on read

Option B — the "proper" one: a materialised FrequentItem model. A row per (userId, normalised name) holding count and lastUsedAt, upserted on every add. Reading the top 8 is one cheap query. The costs: a new table (sandbox deploy), upsert discipline in every add path, and — to make the counts authoritative rather than best-effort — eventually a DynamoDB Streams Lambda doing the increments server-side. That's real machinery for what is, today, a strip of eight chips.

Option A — the honest MVP: derive it from history, client-side. Every item the user ever added is already sitting in the ShoppingItem table. Fetch the user's rows, count by name in JS, rank, done. Zero backend changes, ships the same afternoon. The cost is proportional to history: you re-read everything to re-rank, which is fine for hundreds of rows and indefensible for millions.

I have one user (me) and a few weeks of shopping history. A shipped. But "A, carelessly" and "A, with budgets" are different features — the rest of this post is the budgets.

The scan, with limits it can't blow through

The naive version of option A fetches unboundedly — history only grows. So the scan gets explicit caps: page size, a page ceiling, and a cache TTL so it doesn't run on every render:

const MIN_COUNT = 2;   // "frequent" = added on more than one occasion
const POOL = 40;       // ranked-pool cap; the screen slices to its display limit
const MAX_PAGES = 10;  // pagination guard (~2,000 rows at limit 200)

export function useFrequentItems() {
  return useQuery({
    queryKey: ["frequentItems"],
    staleTime: 5 * 60 * 1000, // re-rank at most every 5 minutes
    queryFn: async (): Promise<FrequentItem[]> => {
      const rows: Row[] = [];
      let nextToken: string | null | undefined;
      let pages = 0;
      do {
        const page = await client.models.ShoppingItem.list({
          filter: { userId: { eq: userId } },
          limit: 200,
          nextToken,
        });
        rows.push(...page.data);
        nextToken = page.nextToken;
      } while (nextToken && ++pages < MAX_PAGES);
      // … aggregate below
    },
  });
}
Enter fullscreen mode Exit fullscreen mode

If history ever exceeds the ~2,000-row budget, the ranking quietly degrades to "your most frequent among recent history" instead of falling over — and that's also the tripwire that says it's time for option B.

The aggregation: a Map and two tie-breakers

Counting is a Map keyed on a normalised name — trimmed, lowercased, whitespace-collapsed — so "Oat milk" and "oat milk" are one item:

export function frequentKey(name: string): string {
  return name.trim().toLowerCase().replace(/\s+/g, " ");
}
Enter fullscreen mode Exit fullscreen mode

Each occurrence bumps the count, and the freshest occurrence wins the display details — casing, unit, category, quantity — so a chip doesn't just add "Milk", it adds your usual milk:

cur.count += 1;
if (at >= cur.lastAt) {
  // Freshest occurrence wins the display casing + pre-fill defaults.
  cur.lastAt = at;
  cur.name = it.name.trim();
  cur.unit = it.unit;
  cur.category = it.category;
  cur.quantity = it.quantity;
}
Enter fullscreen mode Exit fullscreen mode

Then rank by count with a recency tie-break, drop one-offs, and keep a pool the screen can slice:

return [...map.values()]
  .filter((a) => a.count >= MIN_COUNT)
  .sort((a, b) => b.count - a.count || b.lastAt - a.lastAt)
  .slice(0, POOL);
Enter fullscreen mode Exit fullscreen mode

Making the counts honest

Two filtering decisions mattered more than the counting itself.

Soft-deleted and checked-off rows are included. This app never hard-deletes user data — a business "delete" flips _deleted flags (the convention every model in this backend carries). That rule was made for auditability, but here it pays a dividend nobody planned: a checked-off, cleared-away item is the strongest possible repeat-purchase signal. You bought it, it left the list — that's precisely the history worth mining. If deletes had been real deletes, this feature would have no data.

Template lists are excluded. The app also has reusable template lists ("Weekly essentials" — next post). A template's items are a saved set, not a shopping occasion; if they counted, instantiating a template would double-count everything in it forever:

// Template lists are saved sets, not shopping occasions — drop their items
// so they don't inflate the frequency counts.
const templateIds = new Set(userLists.filter((l) => l.isTemplate).map((l) => l.id));
for (const it of rows) {
  if (templateIds.has(it.listId)) continue;
  // … count it
}
Enter fullscreen mode Exit fullscreen mode

Aggregation is easy; deciding what deserves to be aggregated is the actual design work.

The chips themselves

The screen subtracts anything already on the active list — a chip for something you've already added is noise — using the same normalised key:

const frequentChips = useMemo(() => {
  const onList = new Set(items.map((i) => frequentKey(i.name)));
  return frequentPool.filter((f) => !onList.has(f.key)).slice(0, MAX_CHIPS); // 8
}, [frequentPool, items]);
Enter fullscreen mode Exit fullscreen mode

They render in two places: a horizontal strip riding just above the capture bar, and a wrapped cluster in the empty state — a brand-new list greeting you with your usual instead of a void. Tapping one goes through the same addItems mutation as everything else, which means chip-adds inherit the offline queue from Part 7 for free.

The frequently-bought strip riding above the capture bar: one-tap mint chips of your usual items

A brand-new list's empty state: instead of a void, a wrapped cluster of your usuals ready to add

The exit ramp, written down in advance

Option A's failure mode is known and gradual: history outgrows the scan budget, or the re-ranking read cost stops being funny. The upgrade is already designed — the materialised model:

// NOT shipped — the documented option B
FrequentItem: a.model({
  userId: a.string().required(),
  nameKey: a.string().required(),  // frequentKey(name) — the dedup key
  name: a.string().required(),
  count: a.integer(),
  lastUsedAt: a.datetime(),
  unit: a.string(),
  category: a.string(),
}),
Enter fullscreen mode Exit fullscreen mode

Upsert client-side in the add path first (a rare double-count race is harmless for chips), and when the counts need to be authoritative, move the increment into a DynamoDB Streams Lambda on ShoppingItem inserts. The important part isn't the design — it's that it's written down next to the shipped code, so future-me switches when the tripwire fires instead of re-deriving the whole debate.

What I took away

  • NoSQL moves aggregation to write-time or read-time — there is no free GROUP BY. Materialise when data is big or shared; scan client-side when it's personal-scale. Both are legitimate; drifting between them accidentally is not.
  • A client scan without budgets is a time bomb. Page caps, pool caps, and a cache TTL turn "unbounded read" into "bounded, degradable read."
  • Your soft-deletes are a dataset. Never hard-deleting user data was an auditability rule; it quietly made this feature possible.
  • Ship the MVP and the exit ramp. The cheap version is only cheap if the upgrade path is already designed.

Next up

That isTemplate flag doing quiet work in the exclusion filter is a whole feature: reusable template lists — "Weekly essentials," saved once, spun up fresh in one tap — built with zero new models. Why a template is just a list with a flag: Post 11.

Where do you do your aggregation on DynamoDB — Streams-materialised counters, client-side counting, or "we export to something with SQL and don't talk about it"? Genuinely curious where people draw the line.

Top comments (0)