querying table byy array elements

[spacewiz]

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 !

[Noel Grandin]

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).

[spacewiz]

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

[Noel Grandin]

If you want indexing the right answer is to normalise your data i.e.
split the array data out into a separate table

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>

[Lukas Eder]

Hello,

I do agree with Noel, that normalising the schema will produce the best performance as indexes can be used. But for the record, it might also be interesting for H2 to support some of Postgres' array functions in the future:

http://www.postgresql.org/docs/9.0/static/functions-array.html

This might be something to put on the (low-prio) road-map?

Cheers
Lukas

[Noel Grandin]

patches are welcome :-)

> --
> You received this message because you are subscribed to the Google
> Groups "H2 Database" group.

> To view this discussion on the web visit
> https://groups.google.com/d/msg/h2-database/-/tXWvWpF31fMJ.

[Lukas Eder]

Why not? :-)
I'll have a look later...

2012/2/1 Noel Grandin <noelg...@gmail.com>:

[Noel Grandin]

ARRAY_CONTAINS just added to main branch.
Will be in next release.

[spacewiz]

Noel,

Thank you!