Filter rows based on contents in JSON array

I have a SQLite database which contains a table that has a text column. In this column I store a JSON array of strings.

This is the table, shortened to include only what's relevant:

CREATE TABLE "recipePreview" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"name" TEXT NOT NULL, "intro" TEXT,
"tags" TEXT
)

And some sample data (hand written might contain syntax errors...):

INSERT INTO recipePreview (id, name, tags) VALUES (NULL, "My recipe", '["glutenFree","raw","naturallySweetened","vegan"]')
INSERT INTO recipePreview (id, name, tags) VALUES (NULL, "My other recipe", '["aquafaba","glutenFree","vegan"]')

I want to filter these rows based on contents of the JSON array in the tags column. For example; a query that returns all of the recipes that are tagged with raw. I can do that using this query:

SELECT * FROM recipePreview, json_tree(recipePreview.tags, '$')
    WHERE value = "a tag"

What I haven't been able to figure out though is if there is a way to have an AND in my WHERE claus. Ie. something that would look something like this:

SELECT * FROM articles, json_tree(recipePreview.tags, '$')
    WHERE value = "a tag" AND value = "another tag"

I don't want to just do a string compare (using LIKE) because one tag might contain parts of another tag. When searching for recipes tagged with candy I don't want to get hits for candy cotton (contrived example).

OR queries work with the following approach as json_tree actually creates "many rows" for each entry in the JSON array.

Is this somehow possible using the JSON1 extension?

Solution

You can use LIKE. Exploit the fact that within the JSON column, each tag is enclosed in ".

WHERE LIKE '%candy%' will return candy and cotton candy.
WHERE LIKE '%"candy"%' will only return candy.

Nesting notwithstanding, you don't even need to use json_tree, since the tags column is ostensibly TEXT. If it is nested, you could select rows WHERE key = 'tags' and (value like '%"a tag"%' AND value like '%"another tag"%'