One Operation, Many Engines: A Cross-Engine Query Mental Model

Summary

• Scenario

  • The project I’m on uses ClickHouse, Spark, and MongoDB as data sources at once, and I often need to write raw SQL directly to grab a target result fast.
  • But I’m not fluent in raw SQL, so I mostly ask AI to generate a snippet. It runs, but I keep getting stuck in the same place: it wrote count(*), and I can’t remember whether there’s also a count(). Which one is ClickHouse-only (it supports both)? Does count(col) count NULLs? And to check whether a value is in an array, ClickHouse wants has(), Spark uses array_contains(), Mongo is $in?

  • One operation: "does this array contain a value?"
      │
      ├─ ClickHouse  →  has(arr, v)
      ├─ Spark       →  array_contains(arr, v)
      └─ MongoDB     →  { f: {$in: [v]} }

• Motivation

  • I lean on AI every time and still never retain it, because all I have is a pile of scattered syntax, not a mental model. So I wanted to learn it once from scratch and reorganize what I know.
  • Also to sort out how to write Mongo queries (I use it sometimes too), plus the exact terms for the fields in Mongo Compass’s sidebar (e.g. collection, which maps to a table in the relational world).

• Final goals

  • Learn a generic way to query the common data types in ClickHouse and Spark: struct, array, byte, and the like.
  • Know the abstract purpose of an operation, then map it onto how each of the three databases writes it, for example “find a substring inside a string” or “check whether an element is in an array.”

Refusing beats quietly being wrong. A query that “looks right but is wrong” is worse than none: it returns a number, the number looks normal, and nobody notices it counted the wrong rows until it’s already in a report. There’s an engineering principle I keep in mind: any path that can produce a “plausible but wrong” result should fail loudly, not pass silently. An AI query you can’t verify sits right on that line.

The counterintuitive turn: think in operations, don’t memorize syntax

The point isn’t to memorize all three sets of syntax; it’s to re-index what you know, from “syntax” to “the intent of an operation.” What you actually do to data comes down to a dozen-odd moves: count everything, count non-NULLs, default a NULL to something, search a substring, check array membership, map/filter an array, roll multiple rows into one array, branch on a condition. Each is an intent, and each engine just spells it differently.

The journey I laid out for this learning trip

1. Place the three engines first: ClickHouse and Spark both speak SQL (so roughly 80% is portable); Mongo is NoSQL, so it’s its own independent query language.
2. Align the vocabulary: table = collection, row = document, column = field. → MongoDB operators and vocabulary
3. Learn the types first: operations on array/struct/map only make sense once you know what “array” actually means in each engine (ClickHouse arrays even start at index 1). → ClickHouse data types, Spark data types
4. Learn the portable core once: COUNT, CASE WHEN, GROUP BY/HAVING, LIKE/ILIKE, IN/BETWEEN/IS NULL, and that’s the 80%. → the portable SQL core, count(*) vs count(col), LIKE / ILIKE substring search
5. Then learn each engine’s own array syntax: ClickHouse’s arrayMap/Filter/Exists/All, multiIf, has → ClickHouse array functions; Spark’s transform/filter/exists/forall, collect_list/set → Spark array functions and collect.
6. Carry the same operations over to Mongo by analogy: $regex ≈ LIKE, $in ≈ IN, $exists checks whether a field is present.

The conclusion up front — the operation × engine table I ended up with

Entries marked † are Mongo syntax that goes beyond the original notes, pulled from standard MongoDB (query or aggregation operators) — double-check before using.

Operation (intent)	Portable SQL	ClickHouse	Spark SQL	MongoDB
Count all rows (incl. NULL)	COUNT(*)	count(*) / count()	count(*)	countDocuments({}) †
Count non-NULL	COUNT(col)	count(col)	count(col)	{col:{$ne:null}} †
Default value for NULL	—	ifNull(x,d)	NVL(x,d)	$ifNull †
Substring search	LIKE / ILIKE	LIKE / ILIKE	LIKE / rlike	{f:{$regex,$options:"i"}}
Array membership check	—	has(arr,v)	array_contains(arr,v)	{f:{$in:[v]}}
Any element matches	—	arrayExists(x->…,arr)	exists(arr,x->…)	$elemMatch †
All elements match	—	arrayAll(x->…,arr)	forall(arr,x->…)	—
Map over array elements	—	arrayMap(x->…,arr)	transform(arr,x->…)	$map †
Filter an array	—	arrayFilter(x->…,arr)	filter(arr,x->…)	$filter †
Roll rows into an array	—	groupArray() / groupUniqArray()	collect_list() / collect_set()	$push / $addToSet †
Conditional branching	CASE WHEN	if() / multiIf()	CASE WHEN ... THEN ... ELSE ...	$cond / $switch †

Closing

By the end I realized there’s very little to actually memorize: one list of operations, plus one table that translates it into each engine’s syntax. AI can help me write SQL, but I have to know what that SQL is actually asking. A query that errors out isn’t the scary one; the scary one is the query that runs perfectly clean and answers the wrong question.

Test yourself → cross-engine query exercises (with answers).

---

References

• ClickHouse SQL Reference — https://clickhouse.com/docs/en/sql-reference
• Spark SQL Built-in Functions — https://spark.apache.org/docs/latest/api/sql/
• MongoDB Query & Aggregation Operators — https://www.mongodb.com/docs/manual/reference/operator/