7. Analytics Using Complex Data Types
Activity 9: Sales Search and Analysis
Solution
- 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 );
- Create the GIN index on the view:
CREATE INDEX customer_search_gin_idx ON customer_search USING GIN(search_vector);
- 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...