Thread: Array Contained By Array Question
How can I search a multidimensional array for an EXACT "subarray" match? The following produces true when I want it to produce false. Obviously, I would only want it to produce true for ARRAY[1,2], ARRAY[3,4], or ARRAY[5,6]. SELECT ARRAY[ARRAY[1,2], ARRAY[3,4], ARRAY[5,6]] @> ARRAY[ARRAY[1,6]]; Any help is greatly appreciated. Thanks. Arthur
On Fri, Jun 10, 2011 at 11:00 AM, Arthur M. Kang <arthurmkang@gmail.com> wrote: > How can I search a multidimensional array for an EXACT "subarray" match? > > The following produces true when I want it to produce false. Obviously, I > would only want it to produce true for ARRAY[1,2], ARRAY[3,4], or > ARRAY[5,6]. > > SELECT ARRAY[ARRAY[1,2], ARRAY[3,4], ARRAY[5,6]] @> ARRAY[ARRAY[1,6]]; > > Any help is greatly appreciated. create or replace function slice_in_array(needle anyarray, haystack anyarray) returns bool as $$ select $1 in (select $2[v:v] from generate_series(1, array_upper($2, 1)) v); $$ language sql immutable; select slice_in_array(array[array[1,2]], ARRAY[ARRAY[1,2], ARRAY[3,4], ARRAY[5,6]]); -- or -- create or replace function slice_in_array(needle anyarray, haystack anyarray) returns bool as $$ select array[$1] in (select $2[v:v] from generate_series(1, array_upper($2, 1)) v); $$ language sql immutable; select slice_in_array(ARRAY[1,2], ARRAY[ARRAY[1,2], ARRAY[3,4], ARRAY[5,6]]); (not really sure what the deal is with the operator) merlin
Merlin, Thanks so much for the reply. I was suspecting that this was the way to get this done. Thanks again for the help! Arthur On 6/10/2011 10:31 AM, Merlin Moncure wrote: > On Fri, Jun 10, 2011 at 11:00 AM, Arthur M. Kang<arthurmkang@gmail.com> wrote: >> How can I search a multidimensional array for an EXACT "subarray" match? >> >> The following produces true when I want it to produce false. Obviously, I >> would only want it to produce true for ARRAY[1,2], ARRAY[3,4], or >> ARRAY[5,6]. >> >> SELECT ARRAY[ARRAY[1,2], ARRAY[3,4], ARRAY[5,6]] @> ARRAY[ARRAY[1,6]]; >> >> Any help is greatly appreciated. > create or replace function slice_in_array(needle anyarray, haystack > anyarray) returns bool as > $$ > select $1 in (select $2[v:v] from generate_series(1, array_upper($2, 1)) v); > $$ language sql immutable; > > select slice_in_array(array[array[1,2]], ARRAY[ARRAY[1,2], ARRAY[3,4], > ARRAY[5,6]]); > > -- or -- > > create or replace function slice_in_array(needle anyarray, haystack > anyarray) returns bool as > $$ > select array[$1] in (select $2[v:v] from generate_series(1, > array_upper($2, 1)) v); > $$ language sql immutable; > > select slice_in_array(ARRAY[1,2], ARRAY[ARRAY[1,2], ARRAY[3,4], ARRAY[5,6]]); > > (not really sure what the deal is with the operator) > > merlin
On Fri, Jun 10, 2011 at 3:38 PM, Arthur M. Kang <arthurmkang@gmail.com> wrote: > Merlin, > > Thanks so much for the reply. I was suspecting that this was the way to get > this done. > > Thanks again for the help! > > Arthur > > On 6/10/2011 10:31 AM, Merlin Moncure wrote: >> >> On Fri, Jun 10, 2011 at 11:00 AM, Arthur M. Kang<arthurmkang@gmail.com> >> wrote: >>> >>> How can I search a multidimensional array for an EXACT "subarray" match? >>> >>> The following produces true when I want it to produce false. Obviously, >>> I >>> would only want it to produce true for ARRAY[1,2], ARRAY[3,4], or >>> ARRAY[5,6]. >>> >>> SELECT ARRAY[ARRAY[1,2], ARRAY[3,4], ARRAY[5,6]] @> ARRAY[ARRAY[1,6]]; >>> >>> Any help is greatly appreciated. >> >> create or replace function slice_in_array(needle anyarray, haystack >> anyarray) returns bool as >> $$ >> select $1 in (select $2[v:v] from generate_series(1, array_upper($2, 1)) >> v); >> $$ language sql immutable; >> >> select slice_in_array(array[array[1,2]], ARRAY[ARRAY[1,2], ARRAY[3,4], >> ARRAY[5,6]]); >> >> -- or -- >> >> create or replace function slice_in_array(needle anyarray, haystack >> anyarray) returns bool as >> $$ >> select array[$1] in (select $2[v:v] from generate_series(1, >> array_upper($2, 1)) v); >> $$ language sql immutable; >> >> select slice_in_array(ARRAY[1,2], ARRAY[ARRAY[1,2], ARRAY[3,4], >> ARRAY[5,6]]); >> >> (not really sure what the deal is with the operator) You're welcome. For posterity, the reason why the @> operator didn't work for you is that it only looks at the 'element' level, not the slice level (since 1 and 6 were each in the complete array, it matched). Not sure if that's the greatest behavior, but it's not 'wrong'. Basically, you can think if it as unnest(a) @> unnest(b). merlin