5. Analytics Using Complex Data Types

Activity 5.01: Sales Search and Analysis

Solution

1. First, create the materialized view on the customer_sales table. In case a view with the same name already exists, execute DROP IF EXISTS statement prior to the CREATE 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:

   Figure 5.27: Sample record from the customer_search table

2. We can now search records based on the salesperson's request for a customer named Danny who purchased a Bat scooter using the following...