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?