How to convert an Array field into String to be used in 'WHERE' clause in Query Service

[mansibedi]

Hi Team,

I am trying to query service for a dataset 'TEST_DATASET" which has an Array field - "products"

I am trying to query in Query Service -

select field1, field2, field3, products

from TEST_DATASET

where VARCHAR(products) like '%pencil%' 

Basically i am trying to check if the 'product' field, which is an Array datatype, contains the value 'pencil'. I am unable to use the array field in WHERE clause without converting to String. I have tried multiple functions - VARCHAR, ARRAY_TO_STRING, TO_STRING, INLIN but none of them are supported and throwing an error. 

Please suggest how do I convert an array field into string to be used in WHERE clause in Query Service.

[hariprasathg]

I think you can use ANY keyword in your where clause, 

SELECT  * from tablename where attribute_name = ANY('{Seth, Mike, Joseph, Joe, John}')

this should scan the array objects with the specific values.

Thanks,

HariP

[mansibedi]

Hi @hariprasathg 

I tried the following syntax -  (changing the position of ' in ANY clause)

1. select * from TEST_DATASET where ENTITLEMENTINFO.EUPID = ANY('{prod1}')

ErrorCode: 08P01 queryId: d3ae9b28-dd4b-44c5-ae86-58286844b2d1 Unknown error encountered. Reason: [cannot resolve '(`entitlementinfo`.`eupid` = any({prod1}))' due to data type mismatch: differing types in '(`entitlementinfo`.`eupid` = any({prod1}))' (array<string> and string).

2. select * from TEST_DATASET where ENTITLEMENTINFO.EUPID = ANY('{'prod1'}')

ErrorCode: 42601 queryId: 92686cba-d1d8-4133-9462-64187966480c Syntax error encountered. Reason: [line 7:58: extraneous input ''}'' expecting {',', ')', 'ORDER'}]

3. select * from TEST_DATASET where ENTITLEMENTINFO.EUPID = ANY({'prod1'})

ErrorCode: 08P01 queryId: d3ae9b28-dd4b-44c5-ae86-58286844b2d1 Unknown error encountered. Reason: [cannot resolve '(`entitlementinfo`.`eupid` = any({prod1}))' due to data type mismatch: differing types in '(`entitlementinfo`.`eupid` = any({prod1}))' (array<string> and string).

But I am getting an errors as mentioned. Please suggest here.

Please note - I want to use it as LIKE condition in SQL syntax, or you can say in CDP segment, when we use : attribute_name 'Contains' prod1... because along with product, there will be product variant associated in the array which we are searching in WHERE clause.. eg : 'prod1_var1', 'prod1_var2', prod2_var1' etc.. and ANY clause is throwing error in this case.

[nnakirikanti · Accepted solution]

@mansibedi  Check the explode option supported via AEP, first explode the array into multiple rows and then do your business logic on derived column. I have used this many times, let me know if you need more details.

Spark Function support by AEP.

https://experienceleague.adobe.com/docs/experience-platform/query/sql/spark-sql-functions.html?lang=...

[mansibedi]

Thank you @nnakirikanti , I tried using the explode function as you suggested and the query is working now. 

Thank you for the support!