Postgresql – Postgres 9.5 Materialized View from a table with jsonb columns

materialized-viewpostgresql

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?

Best Answer

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';
Related Question