5. Analytics Using Complex Data Types
Activity 5.01: Sales Search and Analysis
Solution
- First, create the materialized view on the
customer_sales
table. In case a view with the same name already exists, executeDROP 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 us a table of the following format (output shortened for readability):
SELECT * FROM customer_search LIMIT 1;
The following is the output of the code:
- We can now search records based on the salesperson's request for a customer named Danny who purchased a Bat scooter using the following...