ARRAY_CONTAINS just added to main branch.
Will be in next release.
On 2012-01-31 21:38, spacewiz 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.
