‘Tagging’ a record using multiple criteria

I am using Presto. I want to assign a row with multiple ‘tags’ using different criteria that are not mutually exclusive.

For example, let’s say there’s a table with 4 columns:

|  food      | color  | type        | on_sale |
|------------+--------+-------------+---------|
|  apple     | red    | fruit       | TRUE    |
|  banana    | yellow | fruit       | FALSE   |
|  cornbread | yellow | baked goods | TRUE    |
|  apple pie | brown  | baked goods | TRUE    |

​x
 
|  food      | color  | type        | on_sale ||------------+--------+-------------+---------||  apple     | red    | fruit       | TRUE    ||  banana    | yellow | fruit       | FALSE   ||  cornbread | yellow | baked goods | TRUE    ||  apple pie | brown  | baked goods | TRUE    |​

I’d like to tag each food with tags that I’ve defined, such as red food, red food on sale, yellow baked goods, yellow food on_sale ONLY. In the example, apple matches two tags:red food and red food on sale.

I can’t use CASE (like below) because I would only get back ‘red food’ when I actually want it labeled as both.

CASE 
   WHEN color = 'red' THEN 'red food'
   WHEN color = 'red' AND `on_sale` = TRUE THEN 'red food on sale'
   (and so on)

 
CASE    WHEN color = 'red' THEN 'red food'   WHEN color = 'red' AND `on_sale` = TRUE THEN 'red food on sale'   (and so on)​

Ideally, I want to return an array that can capture more than 1 ‘tag’ or duplicate columns with one line for each tag, but I have no idea how to do that. Any ideas?

Thanks in advance!

Answer

You can use string concatenation:

LTRIM(CASE WHEN color = 'red' THEN ' red food' ELSE '' END ||
      CASE WHEN `on_sale` = TRUE THEN ' on sale' ELSE '' END
      END)

 
LTRIM(CASE WHEN color = 'red' THEN ' red food' ELSE '' END ||      CASE WHEN `on_sale` = TRUE THEN ' on sale' ELSE '' END      END)​

Advertisement

Answer