Expand my Community achievements bar.

Applications for the Community Advisor Program Class of 2025 are NOW OPEN – Apply Today!

Counting an Array<String> in Query Service

Avatar

Level 4

Help please. Trying to use the following Code in Query Services and keep getting an error: 

SELECT _*******.enrichmentData.category AS CATEGORY,
_********.enrichmentData.enrichmentValue AS enrichmentValue,
count(1) eventCount
FROM dataset_aep_profileenrichment
WHERE _*******.enrichmentData.enrichmentType LIKE ('brands')
GROUP BY _******.enrichmentData.category,
_******.enrichmentData.enrichmentValue

 

The error I get is:

Reason: [[DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE] Cannot resolve "_******.enrichmentdata.enrichmenttype LIKE brands" due to data type mismatch: Parameter 1 requires the "STRING" type, however "_******.enrichmentdata.enrichmenttype" has the type "ARRAY<STRING>".;

 

I've tried every variation I can think - please help. 

2 Replies

Avatar

Community Advisor

@montezh2001 Few options to check value in array:

 

1) array_contains

2) contat_ws (convert to string and use LIKE)

3) contat (convert to string and use LIKE)

 

Check the definitions in here : https://experienceleague.adobe.com/en/docs/experience-platform/query/sql/spark-sql-functions

 

Let me know if you need more help on this and mark this thread accordingly.

 

~cheers,

NN.

 

Avatar

Level 4

Hey @montezh2001 you're getting this error because of the mismatch in data types. You're going to want to explode the array first before you use it, so that you can then serialize the results of that exploded array. The query should look something like this - 

SELECT _*******.enrichmentData.category AS CATEGORY,
_********.enrichmentData.enrichmentValue AS enrichmentValue,
COUNT(1) eventCount
FROM dataset_aep_profileenrichment
LATERAL VIEW EXPLODE(_*******.enrichmentData.enrichmentType) exploded_table AS et -- Explode the array
WHERE et = 'brands' -- Filter the exploded rows (case-sensitive)
GROUP BY _******.enrichmentData.category,
_******.enrichmentData.enrichmentValue;

Let me know if that works for you or if you need any other help

Best,
Tyler