Counting an Array<String> in Query Service

[montezh2001]

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. 

[nnakirikanti · Accepted solution]

@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.

[TylerKrause]

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