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

Summary

• Scenario
  • The project I am on uses ClickHouse, Spark, and MongoDB as data sources at once. I often need to write raw SQL directly to grab a target result fast.
  • But I am 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 cannot remember whether there is 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?

• Motivation

  • I lean on AI every time and still never retain it. 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).
  • Also to sort out the exact terms for the fields in Mongo Compass’s sidebar. For example, collection 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 is already in a report. There is an engineering principle I keep in mind: any path that can produce a “plausible but wrong” result must fail loudly, not pass silently. An AI query you cannot verify sits right on that line.

The counterintuitive turn: think in operations, do not memorize syntax

The point is not to memorize all three sets of syntax. It is 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 is 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 is 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 is 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 is not 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/