7. Analytics Using Complex Data Types

Activity 9: Sales Search and Analysis

Solution

1. First, create the materialized view on the customer_sales table:

   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
   );

2. Create the GIN index on the view:

   CREATE INDEX customer_search_gin_idx ON customer_search USING GIN(search_vector);

3. We can solve the request by using our new searchable database:

   SELECT
       customer_id,
       customer_json
   FROM customer_search 
   WHERE search_vector @@ plainto_tsquery('english', 'Danny Bat');

   This results in eight matching rows:

   Figure 7.29: Resulting...