Nested array limitations

[c0c0n3]

The problem

Crate (3.x and 4.x) does not support nested arrays, not even in a JSON document. In fact, the reference section on arrays makes it plain:

Currently arrays cannot be nested. Something like array(array(text)) won’t work.

What's surprising though is that the object type doesn't support storing a JSON document containing nested arrays---this doesn't seem to be documented anywhere, so perhaps it's a bug. Here's an example where we create a table with two columns x and y of type object and array(object), respectively. Inserting an object in x that has a plain array field works but trying the same with a nested array fails with a misleading exception message. Trying to insert the same object as an element of an array value in y also fails but at least the exception message tells you why.

create table t (x object, y array(object));

insert into t (x) values ('{ "x": [1] }');
-- ok

insert into t (x) values ('{ "x": [[1, 2], [3, 4]] }');
-- SQLActionException[ColumnValidationException: Validation failed for x: 
--    Cannot cast '{ "x": [[1, 2], [3, 4]] }' to type object]

insert into t (y) values (['{ "x": [[1, 2], [3, 4]] }']);
-- SQLActionException[ElasticsearchParseException: nested arrays are not supported]

Notice nested arrays work in Timescale. For example all the above statements work flawlessly in Timescale.

Proposed solution

Well, I don't have a good one at the moment. One thing we should do though is to at least document this as a limitation when using the Crate backend. Also, we should probably file a bug with the Crate guys so it might get fixed in a future Crate release. Then we should brainstorm and consider this problem along side #147, #36, and #24 to come up with better overall support for arrays.

Additional context

#322, #147, #36, #24.

[github-actions]

Stale issue message

[daminichopra]

HI @c0c0n3 @chicco785 , I would like to contribute this issue.
Also, i have tested the description with cratedb version 4.4+ and 4.6 + versions and still same limitation when using the Crate backend.