How to convert an Array field into String to be used in 'WHERE' clause in Query Service | Community
Skip to main content
M
mansibedi
Level 3
October 23, 2023
Solved

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

  • October 23, 2023
  • 2 replies
  • 4039 views

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.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by nnakirikanti

@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=en

 

2 replies

H
hariprasathg
Level 2
October 25, 2023

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

 

M
mansibediAuthor
Level 3
October 25, 2023

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
    Community Advisor
    nnakirikantiCommunity AdvisorAccepted solution
    Community Advisor
    October 30, 2023

    @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=en

     

    M
    mansibediAuthor
    Level 3
    October 31, 2023

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

    Thank you for the support!

    Terms & ConditionsAccessibility statement

    Loading footer...