support of aggregation and grouping of array fields

[eshubin]

I have a table with array field. When I try to count records where this field is not null, I get an error

SQLActionException[Cannot select non-indexed column 'signals' within grouping or aggregations]

the query is:

select count(signals) from devices;

---

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

[joemoe]

hi @eshubin, you can rewrite this query to SELECT COUNT(*) FROM devices WHERE signals IS NOT NULL;. another possiblity
as objects are not indexed you need to access a specific field of an object.
e.g. SELECT COUNT(signals[0]) FROM devices;

[eshubin]

Well, basically I have a custom aggregation function, which needs to accept array field as an argument, it causes the same error.
Wanted to simplify the ticket description.

[joemoe]

ok. we already have this on our backlog. i upvoted it.

[saulius]

This would be great addition, looking forward to it! We would like to give crate a serious try but support for grouping and aggregating on array columns is one of the main requirements in our BI workflow.

[mfussenegger]

Support for this has been implemented:

cr8 run-crate 2.2.x -- @crash <<EOF
    create table t (x int, signals array(int));
    insert into t (x, signals) values (1, [1, 2]), (2, [3, 4]), (3, null);
    refresh table t;
    select count(signals) from t;
EOF

CREATE OK, 1 row affected  (0.273 sec)
INSERT OK, 3 rows affected  (0.082 sec)
REFRESH OK, 1 row affected  (0.101 sec)
+----------------+
| count(signals) |
+----------------+
|              2 |
+----------------+
SELECT 1 row in set (0.061 sec)