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 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.
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%']