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.
Solved! Go to Solution.
Views
Replies
Total Likes
@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.
Views
Replies
Total Likes
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
Views
Replies
Total Likes
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.
@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.
Views
Replies
Total Likes
Thank you @nnakirikanti , I tried using the explode function as you suggested and the query is working now.
Thank you for the support!
Views
Replies
Total Likes
Views
Likes
Replies
Views
Likes
Replies