7. Analytics Using Complex Data Types
Activity 7.01: Sales Search and Analysis
Solution:
- First, create the materialized view on the
customer_sales
table. If a view with the same name already exists but is not up to date, execute theDROP IF EXISTS
statement prior to theCREATE
statement:DROP MATERIALIZED VIEW IF EXISTS customer_search; CREATE MATERIALIZED VIEW customer_search AS ( SELECT customer_json -> 'customer_id' AS customer_id, customer_json, to_tsvector('english', customer_json) AS search_vector FROM customer_sales );
This gives you a table of the following format (output shortened for readability):
SELECT * FROM customer_search LIMIT 1;
The following is the output of the code. Note that the output cells are too large to fit onto a screen so only the first few words are shown in the screenshot:
...