N-Dimensional Array is a flat array of numbers, but with a hierarchical addressing scheme applied to it, which makes it usable as an N-dimensional array accessed with N indexes.

Hierarchical subdivision of the flat array:

dim 0: |. . . . . .|. . . . . .| -- stride = 6, len = 2
dim 1: |. .|. .|. .|. .|. .|. .| -- stride = 2, len = 3
dim 2: |.|.|.|.|.|.|.|.|.|.|.|.| -- stride = 1, len = 2

Formula to access a member with syntax arr[i,j,k]:

flatIndex = 6i + 2j + k

Note that this is just summing up contributions at each dimension. We can perform it in any order. This fact makes transposing an array a cheap operation.

New SQL syntax

Assuming CREATE TABLE tango (a DOUBLE, b DOUBLE) and CREATE TABLE samba (arr DOUBLE[][]), these work:

• SELECT ARRAY[1.0, 2.0, 3.0] -- DOUBLE array literal
• SELECT ARRAY[1, 2, 3] -- also DOUBLE array literal (auto-cast)
• SELECT ARRAY[a, b] FROM tango
• SELECT ARRAY[[a], [b]] FROM tango
• INSERT INTO samba SELECT ARRAY[[a, a], [b, b]] FROM tango
• CREATE TABLE foxtrot AS (SELECT ARRAY[[a, a], [b, b]] arr FROM tango)
• SELECT arr[1, 2] FROM samba -- returns the value in the DOUBLE[][] array at coordinates (1, 2)
• SELECT arr[1] FROM samba -- returns the DOUBLE[] sub-array at index 1
• SELECT arr[1][2] FROM samba -- same result and performance as arr[1, 2]
• SELECT arr[2:] FROM samba -- a slice without the upper bound takes everything from the lower bound till the end of that dimension. The returned array has the same dimensionality -- in this case, DOUBLE[][].
• SELECT arr[1:100] FROM samba -- a slice with up to 100 elements. If the array is shorter, this returns the entire array.
• SELECT arr[2:3, 3:4] FROM samba -- selects a slice of the DOUBLE[][] array, comprising of the second row and third column. The returned array has two dimensions, each of length 1.
• SELECT arr[2:3, 3:4][1] FROM samba -- first selects the slice as above, then takes the sub-array at index 1 of the slice (part of the 2nd row of the original array, a DOUBLE[])
• SELECT arr[1:, 2] FROM samba -- selects a DOUBLE[] subarray of all the 2nd elements in the 2nd dimension
• etc. all other combinations of slicing and indexing
• SELECT a * a FROM samba -- element-wise multiplication of arrays with same shape
• SELECT a + a FROM samba -- element-wise addition of arrays with same shape
• SELECT matmul(arr, transpose(arr)) FROM samba -- multiplies matrix with its transposed self

Also implemented:

• l2price_arr(target, price_array, size_array)
• equality: SELECT arr1 = arr2 FROM table, SELECT arr1 != arr2 FROM table
• dim_length(arr, 1) -- length of array's 1st dimension (1-based)

MUST HAVE:

Support in ILP clients:

• Java
• C
• C++
• Rust
• Python
• Node.js
• Go

...

• fix Javadoc errors (and keep Javadoc clean) @bluestreak01
• fix formatting (and keep it clean) @bluestreak01
• type literal support, as in create table tab(a double[][]); @bluestreak01
• add SQL syntax and behaviour to create table with ARRAY columns @bluestreak01
• ensure syntax errors are accurately reported around ☝️ @bluestreak01 / @nwoolmer
• support array literals from expression parser (ARRAY[[1,2,bid],[2,3,ask]]) @mtopolnik
• array access function: arr[1, 2] @mtopolnik
• optimize array indexing composition: arr[1][2] -> arr[1, 2] @mtopolnik
• validate array access dimensions earlier @mtopolnik
• array slice: arr[1:3, 2:4] @mtopolnik
• array slice with open upper bound: arr[2:] @mtopolnik
• transpose array: t(arr) @mtopolnik
• matrix multiplication: arr1 * arr2 @mtopolnik
• l2price_arr() implemented for orderbook stored in arrays. It should take two array parameters: price_arr, size_arr. @mtopolnik
• rename l2price_arr() to l2price() and make it work alongside the legacy l2price(). Currently there's a signature clash since l2price() is varargs. @mtopolnik
• optimize array functions for constant arguments
• optimize flat array equality check, currently it's implemented as a default interface method
• add SQL syntax and behaviour to add column (alter table..) @nwoolmer
• ensure syntax errors are accurately reported around ☝️ @nwoolmer
• column type change initial stub: accurate error reporting @nwoolmer
• introduce ArrayFunction into the function framework @bluestreak01
• create rnd_array test function @bluestreak01
• implement & test create as select and insert as select for arrays non-WAL @bluestreak01
• implement & test create as select and insert as select for arrays WAL @bluestreak01
• improve ArrayTypeDriver.arrayToJson() so it works with non-default strides (i.e., transposed array) @mtopolnik
• augment rnd_double_array() to generate null elements and null arrays, as well as configure array shape limits @bluestreak01
• add ARRAY to storage fuzz test system (O3 test, column top, drop/create partitions etc) @bluestreak01
• write JSON & CSV http server tests for double arrays @bluestreak01
• add ARRAY support to ILP https://github.com/questdb/rfc/discussions/116 @kafka1991
• add Web Console (JSON + TXT) support for ARRAY type @bluestreak01
• add split/merge partition tests for ARRAY type @bluestreak01
• add PGWire support for ARRAY type @jerrinot
• add WebConsole support to egress large arrays (e.g. egress partial column values, rather than row-level size granularity) @bluestreak01
• WAL/non-WAL to support arrays filly, including all O3 permutations @bluestreak01
• implement ingress for single array element type - DOUBLE
• implement and test graceful failure when converting a partition with arrays to parquet @jerrinot
• add PGWire support to egress large arrays (e.g. egress partial column values) @mtopolnik
• array_length() function
• allow ARRAY[1, 2] and auto-cast to DOUBLE[]
• allow slice bounds to be larger than actual array, don't throw "Index out of bounds"
• ILP over HTTP to support binary encoded arrays, stream arrays in regardless of their size
• ILP over TCP to support binary encoded arrays, stream arrays in regardless of their size
• ensure you can use DEDUP on a table that has arrays. Array as dedup key is not supported. @mtopolnik
• update docs: "Columns of ARRAY type cannot be used in UPSERT KEYS list" @mtopolnik Add ARRAY as non-dedup type + generally improve text documentation#156
• add tests for union/set SQLs for array type cast or lack of thereof (correct error reporting) @jerrinot
• benchmark ARRAY ingress @mtopolnik
• prevent special NULL value of LONG and other types from occurring in an array
• make sure we ban any other array element type except DOUBLE @mtopolnik
• go through as many sqls as possible and make sure we ban or assert exceptions for arrays @nwoolmer
• test GROUP BY, SORT, JOIN, etc
• test replication @mtopolnik

Stage 2 - data ingress refinement

• add WebConsole support to ingress large arrays from INSERT statement, e.g. send queries as POST request (optionally) @bluestreak01
• integrate array column type with Parquet partitions (ser/des need to support the type) and cover that with tests
• add PGWire support to ingress large arrays (? - partial message parsing, test first)
• array support in Go client
• array support in Node.js client
• array support in .Net client

Stage 3 - data egress

• programmatic PG wire, casual select from array column - extend buffer for SQL temporarily, up to max limit, fail when array is beyond max limit
• support PG COPY for unlimited array streaming - if array beyond max limit
• REST (Web Console) - send array as is below X bytes and sent string preview above X byte. Add something that would allow the Web Console preview more of array data in popup window
• REST - send chunked array fully

Stage X - Misc

• support array column type in DEDUP
• integrate array column type with Parquet partitions (ser/des need to support the type) and cover that with tests
• approx_percentile overload that accepts an array of percents and returns an array of results

Stage 3 - misc

• type cast, e.g. cast scalar to array, string to array, array-to-array