You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@flink.apache.org by Rex Fenley <Re...@remind101.com> on 2020/11/02 05:47:32 UTC
Filter By Value in List
Hello,
I'm trying to filter the rows of a table by whether or not a value exists
in an array column of a table.
Simple example:
table.where("apple".in($"fruits"))
In this example, each row has a "fruits" Array<String> column that could
have 1 or many fruit strings which may or may not be "apple".
However, I keep receiving the following error when I do something similar
to the example above:
"IN operator on incompatible types: String and GenericType<java.util.List>"
Is there any way to accomplish this?
Thanks!
--
Rex Fenley | Software Engineer - Mobile and Backend
Remind.com <https://www.remind.com/> | BLOG <http://blog.remind.com/>
| FOLLOW
US <https://twitter.com/remindhq> | LIKE US
<https://www.facebook.com/remindhq>
Re: Filter By Value in List
Posted by Matthias Pohl <ma...@ververica.com>.
Hi Rex,
after verifying with Timo I created a new issue to address your proposal of
introducing a new operator [1]. Feel free to work on that one if you like.
Best,
Matthias
[1] https://issues.apache.org/jira/browse/FLINK-20148
On Thu, Nov 5, 2020 at 6:35 PM Rex Fenley <Re...@remind101.com> wrote:
> Thanks Timo,
>
> Checking if an element is in an Array does seem like a very useful
> function to have. Is there any plan to add it?
>
> Thanks
>
> On Thu, Nov 5, 2020 at 7:26 AM Timo Walther <tw...@apache.org> wrote:
>
>> Hi Rex,
>>
>> as far as I know, the IN operator only works on tables or a list of
>> literals where the latter one is just a shortcut for multiple OR
>> operations. I would just go with a UDF for this case. In SQL you could
>> do an UNNEST to convert the array into a table and then use the IN
>> operator. But I'm not sure if this is a better solution.
>>
>> Regards,
>> Timo
>>
>>
>>
>> On 04.11.20 01:13, Rex Fenley wrote:
>> > None of the following appear to work either. Flink 1.11.2, Scala 2.12.
>> >
>> > table.filter("apple".in(List("apple")))
>> > [info] org.apache.flink.table.api.ValidationException: IN operator on
>> > incompatible types: String and ObjectArrayTypeInfo<String>.
>> >
>> > table.filter("apple".in(java.util.Arrays.asList("apple")))
>> > [info] org.apache.flink.table.api.ValidationException: IN operator on
>> > incompatible types: String and ObjectArrayTypeInfo<String>.
>> >
>> > table.filter(
>> > "apple".in(newju.ArrayList[String](java.util.Arrays.asList("apple")))
>> > )
>> > [info] org.apache.flink.table.api.ValidationException: IN operator on
>> > incompatible types: String and ObjectArrayTypeInfo<String>.
>> >
>> >
>> > On Tue, Nov 3, 2020 at 2:32 PM Rex Fenley <Rex@remind101.com
>> > <ma...@remind101.com>> wrote:
>> >
>> > Using a custom serializer to make sure I'm using a List<String> does
>> > not help.
>> >
>> > [info] org.apache.flink.table.api.ValidationException: IN operator
>> > on incompatible types: String and List<String>.
>> >
>> > On Tue, Nov 3, 2020 at 12:44 PM Rex Fenley <Rex@remind101.com
>> > <ma...@remind101.com>> wrote:
>> >
>> > For clarification, I'm using Pojo and operating on a column of
>> > this type
>> > publicjava.util.List<String> fruits
>> >
>> > adding the following annotation does not help
>> > @DataTypeHint("ARRAY<STRING NOT NULL>")
>> >
>> > On Mon, Nov 2, 2020 at 7:02 AM Aljoscha Krettek
>> > <aljoscha@apache.org <ma...@apache.org>> wrote:
>> >
>> > I believe this is happening because the type system does not
>> > recognize
>> > that list of Strings as anything special but treats it as a
>> > black-box type.
>> >
>> > @Timo: Would this work with the new type system?
>> >
>> > Best,
>> > Aljoscha
>> >
>> > On 02.11.20 06:47, Rex Fenley wrote:
>> > > Hello,
>> > >
>> > > I'm trying to filter the rows of a table by whether or
>> > not a value exists
>> > > in an array column of a table.
>> > > Simple example:
>> > > table.where("apple".in($"fruits"))
>> > >
>> > > In this example, each row has a "fruits" Array<String>
>> > column that could
>> > > have 1 or many fruit strings which may or may not be
>> "apple".
>> > >
>> > > However, I keep receiving the following error when I do
>> > something similar
>> > > to the example above:
>> > > "IN operator on incompatible types: String and
>> > GenericType<java.util.List>"
>> > >
>> > > Is there any way to accomplish this?
>> > >
>> > > Thanks!
>> > >
>> >
>> >
>> >
>> > --
>> >
>> > Rex Fenley|Software Engineer - Mobile and Backend
>> >
>> >
>> > Remind.com <https://www.remind.com/>| BLOG
>> > <http://blog.remind.com/> | FOLLOW US
>> > <https://twitter.com/remindhq> | LIKE US
>> > <https://www.facebook.com/remindhq>
>> >
>> >
>> >
>> > --
>> >
>> > Rex Fenley|Software Engineer - Mobile and Backend
>> >
>> >
>> > Remind.com <https://www.remind.com/>| BLOG
>> > <http://blog.remind.com/> | FOLLOW US
>> > <https://twitter.com/remindhq> | LIKE US
>> > <https://www.facebook.com/remindhq>
>> >
>> >
>> >
>> > --
>> >
>> > Rex Fenley|Software Engineer - Mobile and Backend
>> >
>> >
>> > Remind.com <https://www.remind.com/>| BLOG <http://blog.remind.com/> |
>> > FOLLOW US <https://twitter.com/remindhq> | LIKE US
>> > <https://www.facebook.com/remindhq>
>> >
>>
>>
>
> --
>
> Rex Fenley | Software Engineer - Mobile and Backend
>
>
> Remind.com <https://www.remind.com/> | BLOG <http://blog.remind.com/> |
> FOLLOW US <https://twitter.com/remindhq> | LIKE US
> <https://www.facebook.com/remindhq>
>
Re: Filter By Value in List
Posted by Rex Fenley <Re...@remind101.com>.
Thanks Timo,
Checking if an element is in an Array does seem like a very useful function
to have. Is there any plan to add it?
Thanks
On Thu, Nov 5, 2020 at 7:26 AM Timo Walther <tw...@apache.org> wrote:
> Hi Rex,
>
> as far as I know, the IN operator only works on tables or a list of
> literals where the latter one is just a shortcut for multiple OR
> operations. I would just go with a UDF for this case. In SQL you could
> do an UNNEST to convert the array into a table and then use the IN
> operator. But I'm not sure if this is a better solution.
>
> Regards,
> Timo
>
>
>
> On 04.11.20 01:13, Rex Fenley wrote:
> > None of the following appear to work either. Flink 1.11.2, Scala 2.12.
> >
> > table.filter("apple".in(List("apple")))
> > [info] org.apache.flink.table.api.ValidationException: IN operator on
> > incompatible types: String and ObjectArrayTypeInfo<String>.
> >
> > table.filter("apple".in(java.util.Arrays.asList("apple")))
> > [info] org.apache.flink.table.api.ValidationException: IN operator on
> > incompatible types: String and ObjectArrayTypeInfo<String>.
> >
> > table.filter(
> > "apple".in(newju.ArrayList[String](java.util.Arrays.asList("apple")))
> > )
> > [info] org.apache.flink.table.api.ValidationException: IN operator on
> > incompatible types: String and ObjectArrayTypeInfo<String>.
> >
> >
> > On Tue, Nov 3, 2020 at 2:32 PM Rex Fenley <Rex@remind101.com
> > <ma...@remind101.com>> wrote:
> >
> > Using a custom serializer to make sure I'm using a List<String> does
> > not help.
> >
> > [info] org.apache.flink.table.api.ValidationException: IN operator
> > on incompatible types: String and List<String>.
> >
> > On Tue, Nov 3, 2020 at 12:44 PM Rex Fenley <Rex@remind101.com
> > <ma...@remind101.com>> wrote:
> >
> > For clarification, I'm using Pojo and operating on a column of
> > this type
> > publicjava.util.List<String> fruits
> >
> > adding the following annotation does not help
> > @DataTypeHint("ARRAY<STRING NOT NULL>")
> >
> > On Mon, Nov 2, 2020 at 7:02 AM Aljoscha Krettek
> > <aljoscha@apache.org <ma...@apache.org>> wrote:
> >
> > I believe this is happening because the type system does not
> > recognize
> > that list of Strings as anything special but treats it as a
> > black-box type.
> >
> > @Timo: Would this work with the new type system?
> >
> > Best,
> > Aljoscha
> >
> > On 02.11.20 06:47, Rex Fenley wrote:
> > > Hello,
> > >
> > > I'm trying to filter the rows of a table by whether or
> > not a value exists
> > > in an array column of a table.
> > > Simple example:
> > > table.where("apple".in($"fruits"))
> > >
> > > In this example, each row has a "fruits" Array<String>
> > column that could
> > > have 1 or many fruit strings which may or may not be
> "apple".
> > >
> > > However, I keep receiving the following error when I do
> > something similar
> > > to the example above:
> > > "IN operator on incompatible types: String and
> > GenericType<java.util.List>"
> > >
> > > Is there any way to accomplish this?
> > >
> > > Thanks!
> > >
> >
> >
> >
> > --
> >
> > Rex Fenley|Software Engineer - Mobile and Backend
> >
> >
> > Remind.com <https://www.remind.com/>| BLOG
> > <http://blog.remind.com/> | FOLLOW US
> > <https://twitter.com/remindhq> | LIKE US
> > <https://www.facebook.com/remindhq>
> >
> >
> >
> > --
> >
> > Rex Fenley|Software Engineer - Mobile and Backend
> >
> >
> > Remind.com <https://www.remind.com/>| BLOG
> > <http://blog.remind.com/> | FOLLOW US
> > <https://twitter.com/remindhq> | LIKE US
> > <https://www.facebook.com/remindhq>
> >
> >
> >
> > --
> >
> > Rex Fenley|Software Engineer - Mobile and Backend
> >
> >
> > Remind.com <https://www.remind.com/>| BLOG <http://blog.remind.com/> |
> > FOLLOW US <https://twitter.com/remindhq> | LIKE US
> > <https://www.facebook.com/remindhq>
> >
>
>
--
Rex Fenley | Software Engineer - Mobile and Backend
Remind.com <https://www.remind.com/> | BLOG <http://blog.remind.com/>
| FOLLOW
US <https://twitter.com/remindhq> | LIKE US
<https://www.facebook.com/remindhq>
Re: Filter By Value in List
Posted by Timo Walther <tw...@apache.org>.
Hi Rex,
as far as I know, the IN operator only works on tables or a list of
literals where the latter one is just a shortcut for multiple OR
operations. I would just go with a UDF for this case. In SQL you could
do an UNNEST to convert the array into a table and then use the IN
operator. But I'm not sure if this is a better solution.
Regards,
Timo
On 04.11.20 01:13, Rex Fenley wrote:
> None of the following appear to work either. Flink 1.11.2, Scala 2.12.
>
> table.filter("apple".in(List("apple")))
> [info] org.apache.flink.table.api.ValidationException: IN operator on
> incompatible types: String and ObjectArrayTypeInfo<String>.
>
> table.filter("apple".in(java.util.Arrays.asList("apple")))
> [info] org.apache.flink.table.api.ValidationException: IN operator on
> incompatible types: String and ObjectArrayTypeInfo<String>.
>
> table.filter(
> "apple".in(newju.ArrayList[String](java.util.Arrays.asList("apple")))
> )
> [info] org.apache.flink.table.api.ValidationException: IN operator on
> incompatible types: String and ObjectArrayTypeInfo<String>.
>
>
> On Tue, Nov 3, 2020 at 2:32 PM Rex Fenley <Rex@remind101.com
> <ma...@remind101.com>> wrote:
>
> Using a custom serializer to make sure I'm using a List<String> does
> not help.
>
> [info] org.apache.flink.table.api.ValidationException: IN operator
> on incompatible types: String and List<String>.
>
> On Tue, Nov 3, 2020 at 12:44 PM Rex Fenley <Rex@remind101.com
> <ma...@remind101.com>> wrote:
>
> For clarification, I'm using Pojo and operating on a column of
> this type
> publicjava.util.List<String> fruits
>
> adding the following annotation does not help
> @DataTypeHint("ARRAY<STRING NOT NULL>")
>
> On Mon, Nov 2, 2020 at 7:02 AM Aljoscha Krettek
> <aljoscha@apache.org <ma...@apache.org>> wrote:
>
> I believe this is happening because the type system does not
> recognize
> that list of Strings as anything special but treats it as a
> black-box type.
>
> @Timo: Would this work with the new type system?
>
> Best,
> Aljoscha
>
> On 02.11.20 06:47, Rex Fenley wrote:
> > Hello,
> >
> > I'm trying to filter the rows of a table by whether or
> not a value exists
> > in an array column of a table.
> > Simple example:
> > table.where("apple".in($"fruits"))
> >
> > In this example, each row has a "fruits" Array<String>
> column that could
> > have 1 or many fruit strings which may or may not be "apple".
> >
> > However, I keep receiving the following error when I do
> something similar
> > to the example above:
> > "IN operator on incompatible types: String and
> GenericType<java.util.List>"
> >
> > Is there any way to accomplish this?
> >
> > Thanks!
> >
>
>
>
> --
>
> Rex Fenley|Software Engineer - Mobile and Backend
>
>
> Remind.com <https://www.remind.com/>| BLOG
> <http://blog.remind.com/> | FOLLOW US
> <https://twitter.com/remindhq> | LIKE US
> <https://www.facebook.com/remindhq>
>
>
>
> --
>
> Rex Fenley|Software Engineer - Mobile and Backend
>
>
> Remind.com <https://www.remind.com/>| BLOG
> <http://blog.remind.com/> | FOLLOW US
> <https://twitter.com/remindhq> | LIKE US
> <https://www.facebook.com/remindhq>
>
>
>
> --
>
> Rex Fenley|Software Engineer - Mobile and Backend
>
>
> Remind.com <https://www.remind.com/>| BLOG <http://blog.remind.com/> |
> FOLLOW US <https://twitter.com/remindhq> | LIKE US
> <https://www.facebook.com/remindhq>
>
Re: Filter By Value in List
Posted by Rex Fenley <Re...@remind101.com>.
None of the following appear to work either. Flink 1.11.2, Scala 2.12.
table.filter("apple".in(List("apple")))
[info] org.apache.flink.table.api.ValidationException: IN operator on
incompatible types: String and ObjectArrayTypeInfo<String>.
table.filter("apple".in(java.util.Arrays.asList("apple")))
[info] org.apache.flink.table.api.ValidationException: IN operator on
incompatible types: String and ObjectArrayTypeInfo<String>.
table.filter(
"apple".in(new ju.ArrayList[String](java.util.Arrays.asList("apple")))
)
[info] org.apache.flink.table.api.ValidationException: IN operator on
incompatible types: String and ObjectArrayTypeInfo<String>.
On Tue, Nov 3, 2020 at 2:32 PM Rex Fenley <Re...@remind101.com> wrote:
> Using a custom serializer to make sure I'm using a List<String> does not
> help.
>
> [info] org.apache.flink.table.api.ValidationException: IN operator on
> incompatible types: String and List<String>.
>
> On Tue, Nov 3, 2020 at 12:44 PM Rex Fenley <Re...@remind101.com> wrote:
>
>> For clarification, I'm using Pojo and operating on a column of this type
>> public java.util.List<String> fruits
>>
>> adding the following annotation does not help
>> @DataTypeHint("ARRAY<STRING NOT NULL>")
>>
>> On Mon, Nov 2, 2020 at 7:02 AM Aljoscha Krettek <al...@apache.org>
>> wrote:
>>
>>> I believe this is happening because the type system does not recognize
>>> that list of Strings as anything special but treats it as a black-box
>>> type.
>>>
>>> @Timo: Would this work with the new type system?
>>>
>>> Best,
>>> Aljoscha
>>>
>>> On 02.11.20 06:47, Rex Fenley wrote:
>>> > Hello,
>>> >
>>> > I'm trying to filter the rows of a table by whether or not a value
>>> exists
>>> > in an array column of a table.
>>> > Simple example:
>>> > table.where("apple".in($"fruits"))
>>> >
>>> > In this example, each row has a "fruits" Array<String> column that
>>> could
>>> > have 1 or many fruit strings which may or may not be "apple".
>>> >
>>> > However, I keep receiving the following error when I do something
>>> similar
>>> > to the example above:
>>> > "IN operator on incompatible types: String and
>>> GenericType<java.util.List>"
>>> >
>>> > Is there any way to accomplish this?
>>> >
>>> > Thanks!
>>> >
>>>
>>>
>>
>> --
>>
>> Rex Fenley | Software Engineer - Mobile and Backend
>>
>>
>> Remind.com <https://www.remind.com/> | BLOG <http://blog.remind.com/>
>> | FOLLOW US <https://twitter.com/remindhq> | LIKE US
>> <https://www.facebook.com/remindhq>
>>
>
>
> --
>
> Rex Fenley | Software Engineer - Mobile and Backend
>
>
> Remind.com <https://www.remind.com/> | BLOG <http://blog.remind.com/> |
> FOLLOW US <https://twitter.com/remindhq> | LIKE US
> <https://www.facebook.com/remindhq>
>
--
Rex Fenley | Software Engineer - Mobile and Backend
Remind.com <https://www.remind.com/> | BLOG <http://blog.remind.com/>
| FOLLOW
US <https://twitter.com/remindhq> | LIKE US
<https://www.facebook.com/remindhq>
Re: Filter By Value in List
Posted by Rex Fenley <Re...@remind101.com>.
Using a custom serializer to make sure I'm using a List<String> does not
help.
[info] org.apache.flink.table.api.ValidationException: IN operator on
incompatible types: String and List<String>.
On Tue, Nov 3, 2020 at 12:44 PM Rex Fenley <Re...@remind101.com> wrote:
> For clarification, I'm using Pojo and operating on a column of this type
> public java.util.List<String> fruits
>
> adding the following annotation does not help
> @DataTypeHint("ARRAY<STRING NOT NULL>")
>
> On Mon, Nov 2, 2020 at 7:02 AM Aljoscha Krettek <al...@apache.org>
> wrote:
>
>> I believe this is happening because the type system does not recognize
>> that list of Strings as anything special but treats it as a black-box
>> type.
>>
>> @Timo: Would this work with the new type system?
>>
>> Best,
>> Aljoscha
>>
>> On 02.11.20 06:47, Rex Fenley wrote:
>> > Hello,
>> >
>> > I'm trying to filter the rows of a table by whether or not a value
>> exists
>> > in an array column of a table.
>> > Simple example:
>> > table.where("apple".in($"fruits"))
>> >
>> > In this example, each row has a "fruits" Array<String> column that could
>> > have 1 or many fruit strings which may or may not be "apple".
>> >
>> > However, I keep receiving the following error when I do something
>> similar
>> > to the example above:
>> > "IN operator on incompatible types: String and
>> GenericType<java.util.List>"
>> >
>> > Is there any way to accomplish this?
>> >
>> > Thanks!
>> >
>>
>>
>
> --
>
> Rex Fenley | Software Engineer - Mobile and Backend
>
>
> Remind.com <https://www.remind.com/> | BLOG <http://blog.remind.com/> |
> FOLLOW US <https://twitter.com/remindhq> | LIKE US
> <https://www.facebook.com/remindhq>
>
--
Rex Fenley | Software Engineer - Mobile and Backend
Remind.com <https://www.remind.com/> | BLOG <http://blog.remind.com/>
| FOLLOW
US <https://twitter.com/remindhq> | LIKE US
<https://www.facebook.com/remindhq>
Re: Filter By Value in List
Posted by Rex Fenley <Re...@remind101.com>.
For clarification, I'm using Pojo and operating on a column of this type
public java.util.List<String> fruits
adding the following annotation does not help
@DataTypeHint("ARRAY<STRING NOT NULL>")
On Mon, Nov 2, 2020 at 7:02 AM Aljoscha Krettek <al...@apache.org> wrote:
> I believe this is happening because the type system does not recognize
> that list of Strings as anything special but treats it as a black-box type.
>
> @Timo: Would this work with the new type system?
>
> Best,
> Aljoscha
>
> On 02.11.20 06:47, Rex Fenley wrote:
> > Hello,
> >
> > I'm trying to filter the rows of a table by whether or not a value exists
> > in an array column of a table.
> > Simple example:
> > table.where("apple".in($"fruits"))
> >
> > In this example, each row has a "fruits" Array<String> column that could
> > have 1 or many fruit strings which may or may not be "apple".
> >
> > However, I keep receiving the following error when I do something similar
> > to the example above:
> > "IN operator on incompatible types: String and
> GenericType<java.util.List>"
> >
> > Is there any way to accomplish this?
> >
> > Thanks!
> >
>
>
--
Rex Fenley | Software Engineer - Mobile and Backend
Remind.com <https://www.remind.com/> | BLOG <http://blog.remind.com/>
| FOLLOW
US <https://twitter.com/remindhq> | LIKE US
<https://www.facebook.com/remindhq>
Re: Filter By Value in List
Posted by Aljoscha Krettek <al...@apache.org>.
I believe this is happening because the type system does not recognize
that list of Strings as anything special but treats it as a black-box type.
@Timo: Would this work with the new type system?
Best,
Aljoscha
On 02.11.20 06:47, Rex Fenley wrote:
> Hello,
>
> I'm trying to filter the rows of a table by whether or not a value exists
> in an array column of a table.
> Simple example:
> table.where("apple".in($"fruits"))
>
> In this example, each row has a "fruits" Array<String> column that could
> have 1 or many fruit strings which may or may not be "apple".
>
> However, I keep receiving the following error when I do something similar
> to the example above:
> "IN operator on incompatible types: String and GenericType<java.util.List>"
>
> Is there any way to accomplish this?
>
> Thanks!
>