If you want indexing the right answer is to normalise your data i.e. split the array data out into a separate table
On Tue, Jan 31, 2012 at 21:38, spacewiz <[email protected]> wrote: > Noel, > > Yes, I believe ARRAY_CONTAINS() would be a useful function to have. In > addition, if you could add a feature that would allow to query the > whole table for records that have a specific element in an ARRAY > column - that would be a really powerful feature, especially if it was > possible to index that column - basically index all elements in all > arrays column values to speed up the query I mentioned above. > > Thank you, > Oleg > > > > On Jan 31, 12:47 am, Noel Grandin <[email protected]> wrote: >> we currently have ARRAY_GET and ARRAY_LENGTH functions, but no >> ARRAY_CONTAINS. >> But it shouldn't be hard to add if you need it. >> >> On 2012-01-20 00:30, spacewiz wrote: >> >> >> >> >> >> >> >> > Hello, >> >> > Let me start by saying that I'm very impressed with H2, it's features >> > and performance. I 'm using it for a new project (software implemented >> > in Scala& Java). >> > Currently I'm trying to figure out whether it's possible to query a >> > database table with and ARRAY column to find all records where array >> > contains a given element. >> >> > Something like this: >> >> > =============== >> > drop table if exists arr; >> > create table arr (id identity, arcol array); >> > insert into arr values (1,('a','b')), (2, ('b','c')), (3, >> > ('a','c','d')); >> >> > --And run a query that would give me all records that contain array >> > element 'b' >> > select * from arr a where 'b' in (a.arcol); >> > -- should return records 1 and 2. >> > ================ >> > Also, ideally it should be able to use an index so that I can search a >> > table with up to 1million rows super-quickly. >> >> > ----- >> > To give some background: one of my columns will be a text string >> > (varchar), which I'm planning to split by space chars and put the >> > words in a separate computed column of type ARRAY. I'd like to be able >> > to search for records that have a specific word in computed column's >> > array , and looking to do it super-fast :) Oh, and this search by >> > array elements will actually be inside a "not exists() " clause of >> > another query, to make it more interesting. >> >> > Currently i'm doing it with locate() function : >> > select * from abc where ...... and not exists( select * from blah >> > locate(' '||abc.somecolumn||' ', ' '||blah.blahcolumn' ')<> 0); >> >> > I would really appreciate any hints or alternative approaches to solve >> > this problem. >> >> > Thank you ! > > -- > You received this message because you are subscribed to the Google Groups "H2 > Database" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
