How do I impose LIKE operator on an array in Big Query?-CodePudding

I have an array with a set of strings and I am trying to search for these strings in another column of type string. Basically a LIKE operator kind, but with arrays.

What I have:

I have two tables keyword_table and config_table.

Table1: "keyword_table"

category(STRING)  keywords(ARRAY)
fruits            ["orange", "berry", "apple"]
vegetables        ["bean", "carrot", "onion"]

Table2: "config_table"

code(STRING)     item(STRING)
001              blueberry
002              raspberry
003              white onions
004              red onions
005              onion
006              small beans
007              big beans

Expected Output:

code(STRING)     category(STRING)
001              fruits
002              fruits
003              vegetables
004              vegetables
005              vegetables
006              vegetables
007              vegetables

Could someone please help me solve this.

CodePudding user response：

You can apply the following sql query :

with keyword_table AS (
  select 
    "fruits" AS category,
     ["orange", "berry", "apple"] AS keywords
  UNION ALL
  select 
    "vegetables" AS category,
     ["bean", "carrot", "onion"] AS keywords
),

config_table AS (
  select "001" AS code, "orange" AS item
  UNION ALL
  select "002" AS code, "raspberry" AS item
  UNION ALL
  select "003" AS code, "carrot" AS item
  UNION ALL
  select "004" AS code, "red onions" AS item
  UNION ALL
  select "005" AS code, "onion" AS item
  UNION ALL
  select "006" AS code, "small beans" AS item
  UNION ALL
  select "007" AS code, "big beans" AS item
)

select
  code,
  calculatedCategory as category
from
(
  select 
    code,
    item,
    category,
    keywords,
    CASE WHEN item = keyword then category 
          ELSE ''
          END AS calculatedCategory,
  from config_table 
  cross join keyword_table,
  UNNEST(keywords) AS keyword
)
where calculatedCategory <> '';

The result is :

CodePudding user response：

You might consider another approach using a regular expression as well.

SELECT code, category 
  FROM config_table, keyword_table
 WHERE REGEXP_CONTAINS(item, ARRAY_TO_STRING(keywords, '|'));

Query results