title: Grouping by unique values inside a JSONB array
body: <p>Consider the following table structure:</p>
  
  
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE residences (id int, price int, categories jsonb);
  
  INSERT INTO residences VALUES
    (1, 3, '["monkeys", "hamsters", "foxes"]'),
    (2, 5, '["monkeys", "hamsters", "foxes", "foxes"]'),
    (3, 7, '[]'),
    (4, 11, '["turtles"]');
  
  SELECT * FROM residences;
  
   id | price |                categories
  ----+-------+-------------------------------------------
    1 |     3 | ["monkeys", "hamsters", "foxes"]
    2 |     5 | ["monkeys", "hamsters", "foxes", "foxes"]
    3 |     7 | []
    4 |    11 | ["turtles"]
  </code></pre>
  
  <p>Now I would like to know how many residences there are for each category, as well as their sum of prices. The only way I found was to do this was using a sub-query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT category, SUM(price), COUNT(*) AS residences_no
  FROM
    residences a,
    (
      SELECT DISTINCT(jsonb_array_elements(categories)) AS category
      FROM residences
    ) b
  WHERE a.categories @&gt; category
  GROUP BY category
  ORDER BY category;
  
    category  | sum | residences_no
  ------------+-----+---------------
   "foxes"    |   8 |             2
   "hamsters" |   8 |             2
   "monkeys"  |   8 |             2
   "turtles"  |  11 |             1
  </code></pre>
  
  <p>Using <a href="https://www.postgresql.org/docs/current/static/functions-json.html" rel="nofollow">jsonb_array_elements</a> without subquery would return three residences for foxes because of the duplicate entry in the second row. Also the price of the residence would be inflated by 5.</p>
  
  <p>Is there any way to do this without using the sub-query, or any better way to accomplish this result?</p>
  
  <h3>EDIT</h3>
  
  <p>Initially I did not mention the price column.</p>