Selecting Rows By Specific Column Data in PostgreSQL

Question

I'm trying to build a recipe finder app (PERN stack) like SuperCook but I ran into some problems with my postgres table.

Short description of the desired result. I'd like the user to select some ingredients and return recipes that can be made only using those ingredients.

I imported using pgadmin the table from Recipe Dataset and setup my nodejs server and routes.

My problem is that I can't figure out how I can return recipes that contain only the selected ingredients.

WHERE, LIKE and logic operators can't help me as far as I know (but I'm a beginner so I don't know too much) because using them will return me recipes that contain the selected ingredients + any other ingredient.

The only column we care about for a problem like this is the NER column because it contains the ingredients only. I saw that they are written like a javascript object, for example:

"[""sesame seed"", ""soy sauce"", ""honey"", ""vegetable cooking spray"", ""thin green onion strips"", ""grated ginger"", ""chicken""]"

appearing in pgAdmin like this: ["sesame seed", "soy sauce", "honey", "vegetable cooking spray", "thin green onion strips", "grated ginger", "chicken"]

Question: How can I select from my table only the recipes that contain EXACTLY the selected ingredients or at least a COMBINATION of them IN NO PARTICULAR ORDER?

For example:

INPUT: chicken, butter, onion, garlic

OUTPUT: recipe with NER: ["butter", "chicken", "garlic", "onion"]

OR recipe with NER: ["butter", "chicken"]

OR recipe with NER: ["garlic", "chicken"] etc.

Answer 1

One option is to use json functions to have your ingredients (both, user's and dish') in rows. This way you can join the data on every single ingredient, you can calculate how many ingredients you have compared to total number of ingredients needed and get the percentage of it. You also could offer a list of missing ingredients too.

WITH    --  S a m p l e    D a t a :   
  ingredients ( id, dish, ner ) As
    ( Select 1, 'Jewell Ball''S Chicken', '["cream of mushroom soup", "beef", "sour cream", "chicken breasts"]' Union All
      Select 2, 'Creamy Corn', '["frozen corn", "pepper", "cream cheese", "garlic powder", "butter", "salt"]' Union All
      Select 3, 'Chicken Funny', '["chicken gravy", "cream of mushroom soup", "chicken", "shredded cheese"]' Union All
      Select 4, 'Nolan''S Pepper Steak', '["oil", "tomatoes", "green peppers", "water", "onions", "worcestershire sauce"]' Union All
      Select 5, 'Yor sample dish', '["butter", "chicken", "garlic", "onion"]'
    ),

... using json, transpose dish ingredients into rows ...

  dish_ingredients As
    ( Select  i.id, i.dish, Replace(json_array_elements(i.ner::json)::text, '"', '') as dish_ingredient 
      From    ingredients i
    ),

... don't know how you will provide user ingredients data, so I put your question list of ingredients in a cte and do the same as above to get them in rows ....

  user_input As
    ( Select Replace(json_array_elements( array_to_json(string_to_array('chicken, butter, onion, garlic', ', ')) )::text, '"', '') as user_ingredient 
    ), 

... create another cte to combine the dish and user ingredients, prepare the data for different options of using them (have or not ingredient, total ingredients needed, percentage ...)

• resultset of grid cte at the end of the answer

  grid As 
    ( Select    d.id, d.dish, dish_ingredient, ui.user_ingredient, 
                Case When  ui.user_ingredient Is Null Then 1 End as flag_have_not, 
                Case When  ui.user_ingredient Is Not Null Then 1 End as flag_have, 
                -- 
                Count(Case When ui.user_ingredient Is Not Null Then d.id End)
                      Over(Partition By d.id) as cnt_have,
                Count(d.id) Over(Partition By d.id) as cnt_dish_ingredient, 
                --
                Round(Count(Case When ui.user_ingredient Is Not Null Then d.id End)
                            Over(Partition By d.id)::Decimal 
                      * 100 / Count(d.id) Over(Partition By d.id)
                    , 2) as pct_match
      From      dish_ingredients d
      Left Join user_input ui ON( Position(ui.user_ingredient In d.dish_ingredient) > 0 )
    ) 

... here is the main sql - fetching dishes for which user have 50% or more ingredients ...

--    M a i n    S Q L :
Select    id, dish, Max(pct_match) as pct_match,
          string_agg(Case When flag_have_not = 1 Then dish_ingredient End, ',  ' Order By user_ingredient) as missing_ingredients
From      grid
Where     pct_match >= 50
Group  By id, dish 

/*    R e s u l t :
id  dish                pct_match   missing_ingredients
--  ------------------  ---------   ----------------------------------------------------
 3  Chicken Funny           50.00   cream of mushroom soup,  shredded cheese
 5  Yor sample dish        100.00   null                                                   */

NOTE:
You should take care of multiword ingredients and possible missed match or false match as well as other issues ( match using Like maybe). Bellow is the grid cte resultset:

/*    R e s u l t :     ( grid )
id  dish                       dish_ingredient           user_ingredient    flag_have_not   flag_have   cnt_have  cnt_dish_ingredient   pct_match
--  -------------------------  ------------------------  -----------------  -------------   ---------   --------  -------------------   ---------
1   Jewell Ball'S Chicken      cream of mushroom soup    null                           1        null          1                    4       25.00
1   Jewell Ball'S Chicken      beef                      null                           1        null          1                    4       25.00
1   Jewell Ball'S Chicken      sour cream                null                           1        null          1                    4       25.00
1   Jewell Ball'S Chicken      chicken breasts           chicken                     null           1          1                    4       25.00
2   Creamy Corn                frozen corn               null                           1        null          2                    6       33.33
2   Creamy Corn                pepper                    null                           1        null          2                    6       33.33
2   Creamy Corn                cream cheese              null                           1        null          2                    6       33.33
2   Creamy Corn                garlic powder             garlic                      null           1          2                    6       33.33
2   Creamy Corn                butter                    butter                      null           1          2                    6       33.33
2   Creamy Corn                salt                      null                           1        null          2                    6       33.33
3   Chicken Funny              chicken gravy             chicken                     null           1          2                    4       50.00
3   Chicken Funny              cream of mushroom soup    null                           1        null          2                    4       50.00
3   Chicken Funny              chicken                   chicken                     null           1          2                    4       50.00
3   Chicken Funny              shredded cheese           null                           1        null          2                    4       50.00
4   Nolan'S Pepper Steak       oil                       null                           1        null          1                    6       16.67
4   Nolan'S Pepper Steak       tomatoes                  null                           1        null          1                    6       16.67
4   Nolan'S Pepper Steak       green peppers             null                           1        null          1                    6       16.67
4   Nolan'S Pepper Steak       water                     null                           1        null          1                    6       16.67
4   Nolan'S Pepper Steak       onions                    onion                       null           1          1                    6       16.67
4   Nolan'S Pepper Steak       worcestershire sauce      null                           1        null          1                    6       16.67
5   Yor sample dish            butter                    butter                      null           1          4                    4      100.00
5   Yor sample dish            chicken                   chicken                     null           1          4                    4      100.00
5   Yor sample dish            garlic                    garlic                      null           1          4                    4      100.00
5   Yor sample dish            onion                     onion                       null           1          4                    4      100.00      */