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.
