Postgres 9.5 Materialized View from a table with jsonb columns

Question

For the purpose of improving performance of queries on a rather large table (5M+ rows) containing jsonb columns, I would like to create materialized views with data derived from those columns. Following is a simplified version of the problem:

               Definition

CREATE TABLE my_table (id int8, content jsonb);

               Contents

id |  content
-----------------------------------------------------------------------------
 1 | { "customer": "John Doe", "items": {"product": "Whiskey","qty": 3}}
 2 | { "customer": "John Doe", "items": {"product": "Whiskey","qty": 4}}
 3 | { "customer": "Danny Boy", "items": {"product": "Beer","qty": 2}}

Now I'd like to create two materialized views, one will contain all customer names and the other will contain the count of each product.

So the end result should be:

               Materialized View 1 / the names of the customers
customer_name
-------------
John Doe
Danny Boy

               Materialized View 2 / the quantity of all products

product | quantity
------------------
Beer    | 2
Whiskey | 7

---

For the first materialized view I tried the following:

CREATE MATERIALIZED VIEW 
  customers AS 
SELECT DISTINCT 
  content->'customer' AS customer_name FROM my_table;

This works fine, but I am having trouble with the second materialized view.

How should I go about aggregating quantities for each product in an efficient way?

Answer 1

You just need to GROUP BY the product to build the sum() of the quantities of the products.

CREATE MATERIALIZED VIEW products
AS
SELECT "content"->'items'->>'product' "product",
       sum(("content"->'items'->>'qty')::integer) "quantity"
       FROM "my_table"
       GROUP BY "content"->'items'->>'product';