Hi Team, Can you please guide me to solve my use case, I am pretty new to sql as well as Pinot. Use case: I have a field called tags, it contains 0 or more tags but in a string format. For example: tags in some rows = “123, 456, 789”; “123, 456”, “456”; “123”; “907, 789”; “789” If i want all the rows which have tags 123 or 456 in it, I have to write the following query: Select * from qrcodes_updated where tags like '%123%' or tags like '%456%' This will give me 1st, 2nd, 3rd and 4th row. Is it possible to somehow not use like operator and rather in operator such as: tags in (123, 456), and get the same output. Or is the previous query better.? For that I thought, I should derive a new column "tagsArray". Will that help my use case? I have a column named tags in qrcodes_updated table, and I want to derive a new column from it i.e. tagsArray it will contain same data as tags but in an array. I have the following questions: I have added the following in schema: { "name": "tagsArray", "dataType": "STRING", "singleValueField": false, "defaultNullValue": "null" } and added a transform config: { "columnName": "tagsArray", "transformFunction": "Groovy({tags.split(\",\")}, tags)" } But its showing Groovy transform functions are disabled for table config. How can i solve this? 1. After this transformation is done what should be my query. 2. How a query on multivalued field works? 3. Question about which query is better as asked in my use case. 4. Is there any other solution? Thank you.

I think what you want is multi valued column, check docs on the same

Sure, I will read on this. Thank you.

Would something like this work

SELECT *
FROM qrcodes_updated
WHERE REGEXP_LIKE(tags, '123|456');

Sure i will try this, thanks.

This appears to be a use case for a multi-valued column. If you set singleValueField to

false

, then you can use

IN

and related functions. https://docs.pinot.apache.org/configuration-reference/schema#dimensionfieldspec https://docs.pinot.apache.org/configuration-reference/functions/valuein https://docs.pinot.apache.org/users/user-guide-query/query-syntax/supported-aggregations#multi-value-column-functions

Hi @robert zych, yes I’ve done this. But still not able to understand how should I write my query.

In terms of writing the query, if you take a look at the valuein link above, it should be

where tagsArray in ('123', '456')

.

@robert zych Yeah I enabled groovy, its working fine now. I will just work on my query. Thanks.