Chapter 16 — Exercises
Run against
mercado.products.attributesis JSONB; productname/descriptionare searchable text. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.
Group A — JSONB
16.1 Show each product's name and its color attribute (as text). (answer in Appendix)
16.2 Find all products whose attributes contain {"color":"silver"} using the @> operator. (answer in Appendix)
16.3 Find products with ram_gb of 16 or more (extract and cast the value).
16.4 Which products have a wireless attribute key at all (use ?)?
16.5 ⭐ Explain json vs jsonb and why this book uses jsonb. What index type speeds up @> queries on it?
Group B — Full-text search
16.6 Write a query that finds products matching the search 'wireless' in name or description using to_tsvector/to_tsquery/@@. (answer in Appendix)
16.7 Find products matching 'wireless AND speaker' and rank them by ts_rank.
16.8 Contrast this with WHERE name LIKE '%wireless%'. Name two things full-text search does that LIKE cannot. (answer in Appendix)
16.9 ⭐ Add a generated tsvector column + GIN index to products (in a transaction you can roll back) and rewrite 16.6 to use it.
Group C — Arrays
16.10 (Conceptual) Given a hypothetical tags text[] column, write: products tagged 'wireless'; products tagged with all of {'wireless','black'}; and expand tags to one row each. (answer in Appendix)
16.11 ⭐ When is an array column the right choice, and when should you use a junction table instead? Give a Mercado example of each.
Group D — Ranges, series, regex, LATERAL
16.12 Use generate_series to produce one row per month of 2024. (answer in Appendix)
16.13 ⭐ Build a "revenue per month" report that shows every month of 2024 even if some had no orders (join a generate_series spine to monthly revenue with a LEFT JOIN).
16.14 Use a regular expression to find products whose sku matches the pattern LAP-\d{3}.
16.15 ⭐ Use a LATERAL join to get the top 2 most-expensive line items per order.
16.16 ⭐ (Conceptual) Sketch how a daterange column + an exclusion constraint would prevent two overlapping reservations for the same room.
Group E — Judgment (theme #4)
16.17 For each, decide whether PostgreSQL alone suffices or a separate system is warranted, and why: (a) per-product variable specs; (b) catalog search; (c) caching session data with sub-millisecond reads; (d) petabyte-scale log analytics. (answer in Appendix)
16.18 ⭐ A teammate wants to add MongoDB "because our product attributes vary by category." Write a short, friendly rebuttal grounded in this chapter (and Chapter 1's Lumen case).
Group F — Progressive project
16.19 Find one genuine fit in your domain for JSONB, full-text search, a range type, or generate_series, and implement it — with the appropriate index.
16.20 Write one sentence explaining why that feature is the right choice over a separate database or a different model.
Self-check. If you can query and index JSONB, run ranked full-text search, and judge when PostgreSQL replaces a second database (and when it doesn't), you've completed SQL Mastery. On to design.