Counting an Array<String> in Query Service | Community
Skip to main content
M
montezh2001
Level 4
January 28, 2025
Solved

Counting an Array<String> in Query Service

  • January 28, 2025
  • 2 replies
  • 638 views

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. 

    Best answer by nnakirikanti

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

     

    2 replies

    nnakirikanti
    Community Advisor
    nnakirikantiCommunity AdvisorAccepted solution
    Community Advisor
    January 28, 2025

    @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
      Adobe Champion
      TylerKrauseAdobe Champion
      Adobe Champion
      January 31, 2025

      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

      Terms & ConditionsAccessibility statement

      Loading footer...