Sql: Allow array counting about crate

[diyism]

I need "select sum(array_count(array_matched(time_visit))) as total from statis_user_success where any(time_visit)>1481009355". Related to https://www.bountysource.com/issues/39611200-feature-request-support-array_matched-to-filter-elements-by-any-condition

[diyism]

After 3 years and 4 months, any progress?

[joemoe]

@diyism unfortunately not.

[lukasender]

Hey @eMerzh !

we will put that on our backlog.
stay tuned :)

[mfussenegger]

Hi @eMerzh

we are about to start working on this and would like to have a bit more feedback from you regarding your use case.

Either here in the github issue or if you prefer you could also drop by on our irc channel on freenode - if no one is answering just mention someone with op rights directly

[mfussenegger]

Hi @eMerzh

thanks for your feedback so far via IRC.
I've another question regarding the array count feature. Would it also be sufficient for your use case to have a set-count function?

In our index we don't store the array as is but rather generate terms/tokens and our idea is to provide a function which exposes that information. Doing so would be much faster to query on then providing the array count.

[eMerzh]

Hi,
thanks to you!
Not sure to understand the "set-count" part ... but if i'm able to do smth like

select * from myTable where array_lenght(MyColumn) > 3;

or

select * from myTable where MyColumn && [A,B,C];

(&& is "overlap" or have elements in common in postgresql )
then i'm happy :D ...
postgresql might be a good source of inspiration for array function as it support array in core in an sql fashion.

[seut]

Hey,

difference is: set-count would count only distinct values whereas array-count would count all, including duplicates.

[eMerzh]

then, yeah no problem for my use case :)

[devoncrouse]

+1

[joemoe]

@eMerzh we added some array functions in the last time.
https://crate.io/docs/reference/en/latest/sql/scalar.html#array-functions
do you still need the count or overlap?

[eMerzh]

thanks @joemoe :) ... seems better, but how do you count the length of and array now?

[joemoe]

@eMerzh at the moment you can't.
the problem is that for the example you give the query will be either time consuming because the function needs to be applied on every row or we need to build a function based index.
as i interpreted your request it looked like you have growing arrays, each with the same elements. that's why array_intersects would also do its job. is this true?
this would be much faster.

[eMerzh]

yes like you have and array of email... you want to know every users with 2 emails... or smth like that...

[joemoe]

ok thanks. are you still using crate?

[eMerzh]

not currently ... ;-) so not top priority for now ;)

[diyism]

@joemoe, Maybe subquery can help:
select (select count(*) from unnest(tb1.array1)) from tb1
Unfortunately, we can only do:
select (select count(*) from unnest([1,2])) from tb1

In https://crate.io/docs/crate/reference/en/latest/sql/reference/value_expressions.html#scalar-subquery :
...Columns from relations from outside of the subquery cannot be accessed from within the subquery...

Why?

In https://crate.io/docs/crate/reference/en/latest/sql/subquery_expressions.html :
...The used subquery has to be uncorrelated which means that the subquery does not contain references to relations in a parent statement...

Why?

[diyism]

If we have group_concat():
select char_length(regexp_replace(group_concat(tb1.array1)||',', '[^,]*,', ',', 'g'))
but we can only:
select char_length(regexp_replace('ac,b,23'||',', '[^,]*,', ',', 'g'))

[diyism]

Currently we can add another column "count_dates":

insert into tb1 (key, dates, count_dates) values ('id_2', [1508999991], 1);
update tb1 set dates=array_cat(dates, [1509000401]),count_dates=count_dates+1 where key='id_2';
select count_dates from tb1 where key='id_2';

But if i insert duplicated "dates", the count_dates will be incorrect:

update tb1 set dates=array_cat(dates, [1509000401]),count_dates=count_dates+1 where key='id_2';
update tb1 set dates=array_unique(array_cat(dates, [1509000401])),count_dates=count_dates+1 where key='id_2';
select count_dates from tb1 where key='id_2';

[diyism]

The CrateDB 2.3.0 has come, the array count() function is still not available.
I think you can only add an extra count index to all array fields to realize this count() function.

[mfussenegger]

We've added array_upper and
array_length which can be used for
the use-cases here.

They'll be available in 3.2 and can already be used in the latest nightly.

As already mentioned in this issue in some cases performance of using the
scalars in the where clause can be problematic. We've some optimizations
pending but not all cases can be
optimized efficiently.