By Jason Arnold, Co-Founder and Distinguished Engineer
Welcome back. In my previous post, we looked at Ocient’s approach to casting and timestamps, specifically noting how “peeking under the hood” at generated column names can reveal how our engine processes data.
Today, I want to apply that same lens to one of the most powerful features in Ocient: arrays.
While many databases treat arrays as second-class citizens, Ocient treats them as a core part of the data model. We support complex nesting, jagged arrays, high-performance indexing, and some unique predicate syntax that you won’t find in standard Postgres.
Let’s dig in.
Array Literals and Type Inference
First, let’s look at how to write an array literal. As we established last time, the generated column headers are your best friend for understanding data types.
The Results:
Notice the column name array_long. Ocient inferred that these were BIGINT values. But what if you wanted standard integers? Just like with scalar values, Ocient allows for explicit casting.
The Results:
Notice the column name array_long. Ocient inferred that these were BIGINT values. But what if you wanted standard integers? Just like with scalar values, Ocient allows for explicit casting.
The Results:
Things get interesting when you deal with NULLs. If you create an array of purely NULL values, the engine has to guess the type.
The Results:
It defaults to CHAR, but you are never stuck with that decision. You can force an all-NULL array to be whatever type you need.
The Results:
Complexity: Nested and Jagged
Ocient doesn’t require arrays to be perfectly rectangular. We support nested, multi-dimensional arrays, and they can be “jagged” (having different lengths at the same depth).
The Results:
The Operator Toolkit: Contains and Overlap
We support standard operators for checking array contents.
@>(Contains): Check if an array contains a specific value or a subset of values.&&(Overlap): Check if two arrays share any common elements (intersection).
select array [1,2,3} @> 3, array[1,2,3} @> 4
select array [1,2,3} @> array [2,3], array[2,3,4};
select array [1,2,3} && array [3,4,5], array[1,2,3} && array[4,5,6]
The Results:
The “Ocient Special”: Quantifiers (for_some & for_all)
This is where Ocient syntax really shines. We introduce for_some and for_all syntax to apply scalar predicates across array elements. This allows for very expressive filtering without needing to unnest data first.
-
for_some: The condition is true for at least one element. -
for_all: The condition is true for every element.
You can use these with comparison operators (>, <) and even pattern matching (LIKE).
Performance: Indexing Arrays
A common fear with database arrays is performance. In Ocient, you can create indexes on array columns to speed up these predicates. We even support N-gram indexes for text arrays.
Aggregation: Building Arrays
You can construct arrays dynamically from rows using array_agg. A powerful feature here is that you can enforce order inside the aggregation itself, ensuring your resulting lists are deterministic.
The Results:
Unnesting: The Zip vs. The Cross Product
No discussion of arrays is complete without unnest. Ocient follows the standard:
-
unnest(col): Explodes the array into rows. -
with ordinality: Returns the index of the element.
However, there is a nuance when unnesting multiple columns that power users need to know.
If you unnest two columns separately in the select list, you get a Cross Product (Cartesian product):
If you want to keep the arrays aligned (index 1 matches index 1), you use Zip Unnest by passing multiple arguments to a single unnest function.
The Results:
Finally, unlike some systems that flatten everything, Ocient respects your structure. Unnesting a multi-dimensional array only peels back one layer of nesting.
The Results:
Arrays in Ocient are much more than just a storage format; they are a fully indexed, high-performance tool for data modeling. Whether you are using for_some to check conditions without unnesting, or using “Zip Unnest” to align complex datasets, the syntax is designed to be both flexible and precise.
There are many more scalar functions we didn’t cover today (concatenation, deduplication, find-and-replace, etc.), but experimenting with the examples above in sys.dummy is the best way to start building muscle memory.
As always, you can find full documentation of Ocient here.