Build dynamic SQL with multiple AND conditions

Hi there,

I try to create a SQL statement with dynamic number of AND conditions based on the ingredients input field.

Want to generate an SQL statement that looks similar to this.

select 
  ARRAY_AGG(DISTINCT JSON_VALUE(ingredients.ingredient.title) IGNORE NULLS) as ingredient,
from `recipies_json`,
unnest (json_query_array(recipe_data[0].ingredients)) as ingredients
where true
 AND JSON_VALUE(ingredients.ingredient.title) like '%carrot%'
 AND JSON_VALUE(ingredients.ingredient.title) like '%red onion%'
 AND JSON_VALUE(ingredients.ingredient.title) like '%garlic%'

If possible I don't want to disable parametrized SQL statements.
The main challenge I'm facing is that the search_ingredient field could have none, one or multiple entries, so in my SQL statement, I don't know how many times an AND condition is required.

Thank you for any pointers.

Hey @alien, welcome to the community :hugs:

Try

JSON_VALUE(ingredients.ingredient.title) ~* '(carrot|red onion|garlic)'
  • ~ for case sensitive matching
  • ~* for case insensitive matching

You could have '(carrot|red onion|garlic)' be the options from the input.

For cases where this field is empty you'll have to do something like this:

WHERE
  true
  AND (
    {{search_ingredients.value == undefined}}
    OR JSON_VALUE(ingredients.ingredient.title) ~* '(carrot|red onion|garlic)'
  )

Something like that :)) Does that work for you?

Thank you for the suggestion. One thing I didn't mention is that I'm using BigQuery. But even with BigQuery I could use IN operator to find any occurrence. However, I'm after a complete match, i.e. all of the 3 elements must be matched.

Hey @alien!

Not sure what the best answer here is but this seems to work by matching on last_name from a test database against an array, call it arrayToMatch:

SELECT
  last_name
FROM
  test.users
WHERE
  (
    SELECT
      count(*)
    FROM
      UNNEST({{arrayToMatch}}) AS str_to_match
    WHERE
      last_name LIKE str_to_match
  ) = {{arrayToMatch.length}}

My guess is something like the following may work as your WHERE clause:

WHERE
  (
    SELECT
      count(*)
    FROM
      UNNEST({{ingredientArray}}) AS ingredient_to_match
    WHERE
      JSON_VALUE(ingredients.ingredient.title) LIKE ingredient_to_match
  ) = {{ingredientArray.length}}

Can you let me know if it does or doesn't?

Edit: Presumably, ingredientArray would be ['%carrot%', '%red onion%', '%garlic%']

Hi Kabirdas,

thank you so much, I've tried it on BigQuery and it works that way.