Fetch by contains on a field as Array or List

["javastuff.sam@gmail.com" <ja...@gmail.com>]

Hi,

I have an object with one of the field as Array or List. Array/List field
holds Long values. Size could be 10s or 100s or few 1000s, not expecting it
to grow more than 5000. Object won't be updated frequently, mostly read
only.

Object implements Binarylizable.

Requirement is to fetch all the cached objects where Array/List field
contains a give value. Is there any type of SQL query I can use to achieve
this usecase? 

Thanks,
-Sam



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Fetch-by-contains-on-a-field-as-Array-or-List-tp10276.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Fetch by contains on a field as Array or List

[vkulichenko <va...@gmail.com>]

I've never seen such approach :) I doubt it will be efficient, but you can
give it a try.

-Val



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Fetch-by-contains-on-a-field-as-Array-or-List-tp10276p10351.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Fetch by contains on a field as Array or List

["javastuff.sam@gmail.com" <ja...@gmail.com>]

I guess TextQuery can be a option provided Collection field is converted to
String field.  I will try out a sample. 

Any comments or cons for this approach?

-Sam



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Fetch-by-contains-on-a-field-as-Array-or-List-tp10276p10345.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Fetch by contains on a field as Array or List

[vkulichenko <va...@gmail.com>]

Sam,

Collections can't be indexed in Ignite. You can easily implement CONTAINS
function as you described, but as I mentioned, using it will trigger full
scan. 

Another option you have is to implement IndexingSpi and use SpiQuery to
execute queries. But this will imply implementing indexing and querying
logic from scratch, and you will not be able to use full SQL capabilities.

My recommendation would be to split collection into separate collocated
entries.

-Val



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Fetch-by-contains-on-a-field-as-Array-or-List-tp10276p10316.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Fetch by contains on a field as Array or List

["javastuff.sam@gmail.com" <ja...@gmail.com>]

"Search will not be indexed" sounds costly, Is there any other way this can
be done example - LIKE operator in SQL?

Regarding custom function option - 
1. Need to use  "WHERE CONTAINS(ID_LIST) =12345" where CONTAINS is custom
function.
2. From above example where clause ID_LIST will be available as ARRAY/LIST
to perform java logic inside custom function implementation. 

-Sam



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Fetch-by-contains-on-a-field-as-Array-or-List-tp10276p10315.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: Fetch by contains on a field as Array or List

[vkulichenko <va...@gmail.com>]

Hi Sam,

To do the search you can create a custom function that will do the job [1].
But note that this search will not be indexed. To create an index you need
to store each element of collection as a separate cache entry.

https://apacheignite.readme.io/docs/miscellaneous-features#custom-sql-functions

-Val



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/Fetch-by-contains-on-a-field-as-Array-or-List-tp10276p10298.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.