recipes.sql

create table recipes (id integer primary key, name text, ingredients text);
insert into recipes values (1, 'Cake', '["flour", "sugar", "eggs"]');
insert into recipes values (2, 'Pancakes', '["flour", "eggs", "butter"]');
insert into recipes values (3, 'Waffles', '["flour", "milk", "eggs"]');
insert into recipes values (4, 'Pizza', '["flour", "sugar", "eggs", "cheese"]');

select id, name, ingredients, (
  select json_group_array(value) from json_each(ingredients)
  where value in (select value from json_each(:p0))
) as matching_ingredients
from recipes
where json_array_length(matching_ingredients) > 0
order by json_array_length(matching_ingredients) desc

Demo: https://lite.datasette.io/?sql=https://gist.githubusercontent.com/simonw/1f8a91123ccefd8844187225b1832d7a/raw/5069075b86aa79358fbab3d4482d1d269077d632/recipes.sql#/data?sql=select+id%2C+name%2C+ingredients%2C+%28%0A++select+json_group_array%28value%29+from+json_each%28ingredients%29%0A++where+value+in+%28select+value+from+json_each%28%3Ap0%29%29%0A%29+as+matching_ingredients%0Afrom+recipes%0Awhere+json_array_length%28matching_ingredients%29+%3E+0%0Aorder+by+json_array_length%28matching_ingredients%29+desc&p0=%5B%22sugar%22%2C+%22cheese%22%5D

This is an answer to this question on Reddit: https://www.reddit.com/r/sqlite/comments/wr0wp0/i_have_a_sqlite_database_of_recipes_i_would_like/