title: How to aggregate ilike found search on multiple row in PostgreSQL?
body: <p>Let say I have this table</p>
  
  <pre><code>CREATE TEMP TABLE pizzas (
    pizza_id             INT,
    name                 TEXT,
    ingredients          TEXT,
    kingsize_ingredients TEXT
  );
  </code></pre>
  
  <p>and some entries</p>
  
  <pre><code>pizza_id |      name       |       ingredients           |         kingsize_ingredients
  ---------+-----------------+-----------------------------+-------------------------------------
      1    |  moutainview    | cheese,sausage,potato       | cheese,sausage,potato,tomato
      2    |  the o' chicken | cheese,chicken,tomato       | cheese,chicken,tomato,eggs
      3    |  hawai          | mozarella,tomato,pineapple  | mozarella,tomato,pineapple,sausage
  </code></pre>
  
  <p>This is fictive, in this example I stack ingredients in a long comma separated string, I know it is stupid but I try to provide an environment for my question.</p>
  
  <p>So basically, the dummy engine takes a string and search for matches in the <code>pizzas</code> table. The search is performed on multiple columns and <strong>my goal</strong> is to make a search engine that will not only return the name of the pizzas where the search matches but also return the columns in the row where the search did match.</p>
  
  <p>So for instance if I search <em>"tomato"</em> in the previous table, I should have this output : </p>
  
  <pre><code> name           |         found_in
  ----------------+-------------------------------------
   mountainview   | {kingsize_ingredients}
   hawai          | {ingredients,kingsize_ingredients}
   the o' chicken | {ingredients,kingsize_ingredients}
  </code></pre>
  
  <p>Actually I sort of answered my question and here I provide the solution :</p>
  
  <pre><code>SELECT
    name,
    array_agg(found_in) AS found_in
  FROM (
         SELECT
           *,
           'ingredients' AS found_in
         FROM pizzas
         WHERE ingredients ILIKE '%tomato%'
  
         UNION ALL
  
         SELECT
           *,
           'kingsize_ingredients' AS found_in
         FROM pizzas
         WHERE kingsize_ingredients ILIKE '%tomato%'
       ) AS founds
  GROUP BY (name)
  </code></pre>
  
  <p>But I think when it comes to search, optimisation is a priority and because I made this query with my personal knowledge about PostgreSQL (which is not my field of expertise) I am not sure if I am doing that right. This question is not about debating what is the way of making this PostgreSQL query as optimized as possible, it's about asking if this query is having a bad structure or flaws ?</p>
  
  <p>Here is the code if need to initialize at home :</p>
  
  <pre><code>DROP TABLE IF EXISTS pizzas;
  CREATE TEMP TABLE pizzas (
    pizza_id             INT,
    name                 TEXT,
    ingredients          TEXT,
    kingsize_ingredients TEXT
  );
  
  INSERT INTO pizzas VALUES
    (1, 'moutainview', 'cheese,sausage,potato', 'cheese,sausage,potato,tomato'),
    (2, 'the o'' chicken', 'cheese,chicken,tomato', 'cheese,chicken,tomato,eggs'),
    (3, 'hawai', 'mozarella,tomato,pineapple', 'mozarella,tomato,pineapple,sausage');
  </code></pre>
  
  <p>Thanks</p>