Filter By Value in List

[Rex Fenley]

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!

--

[Aljoscha Krettek]

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]

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 <[hidden email]> 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]

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 <[hidden email]> 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 <[hidden email]> 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 |  BLOG  |  FOLLOW US  |  LIKE US

--

[Rex Fenley]

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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 |  BLOG  |  FOLLOW US  |  LIKE US

--

Rex Fenley  |  Software Engineer - Mobile and Backend

Remind.com |  BLOG  |  FOLLOW US  |  LIKE US

--

[Timo Walther]

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 <[hidden email]
> <mailto:[hidden email]>> 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 <[hidden email]
>     <mailto:[hidden email]>> 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
>         <[hidden email] <mailto:[hidden email]>> 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]

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 <[hidden email]> 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 <[hidden email]
> <mailto:[hidden email]>> 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 <[hidden email]
>     <mailto:[hidden email]>> 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
>         <[hidden email] <mailto:[hidden email]>> 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>
>

--

[Matthias]

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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]
> <mailto:[hidden email]>> 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 <[hidden email]
>     <mailto:[hidden email]>> 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
>         <[hidden email] <mailto:[hidden email]>> 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 |  BLOG  |  FOLLOW US  |  LIKE US