Chapter 16 — Quiz

13 questions. Answers at the bottom.

Multiple choice

Q1. jsonb differs from json in that jsonb: - A) Preserves exact input text and whitespace - B) Stores a parsed binary form — faster to query and indexable - C) Cannot be indexed - D) Is read-only

Q2. To extract a JSON field as text, use: - A) -> - B) ->> - C) @> - D) ?

Q3. attributes @> '{"color":"silver"}' tests: - A) Whether the key exists - B) Whether attributes contains that key/value - C) Equality of the whole document - D) Array overlap

Q4. To make JSONB containment queries fast, create: - A) A B-tree index - B) A GIN index - C) No index needed - D) A hash index

Q5. Full-text search matches with which operator? - A) LIKE - B) @@ - C) ~ - D) @>

Q6. to_tsvector('english', text): - A) Stores raw text - B) Normalizes text into lexemes (lowercasing, stemming, removing stop words) - C) Is the search query - D) Ranks results

Q7. Two things full-text search does that LIKE '%x%' cannot: - A) Nothing different - B) Stem words and rank by relevance (and use a GIN index) - C) Only case-insensitive matching - D) Delete rows

Q8. An array column is a good choice for: - A) Data you join and constrain heavily - B) Small, mostly-read-whole lists (e.g., a few tags) - C) Foreign-key relationships - D) Money

Q9. generate_series('2024-01-01'::date, '2024-12-01', '1 month') produces: - A) One row (the first date) - B) One row per month — useful as a report "date spine" - C) An error - D) A JSON array

Q10. Theme #4 of this book says PostgreSQL's features: - A) Replace SQL - B) Often eliminate the need for a separate document/search database - C) Are slower than NoSQL always - D) Should never be used

True/False

Q11. JSONB is a good reason to model genuinely relational data as documents. (True / False)

Q12. Using JSONB or full-text search on a large table without the right index will be slow. (True / False)

Short answer

Q13. A teammate proposes adding MongoDB because "product attributes vary by category." Give a concise counter-argument using this chapter.

---

Answer key

Q1 — B. jsonb is parsed/binary — faster queries, indexable. json keeps raw text. Prefer jsonb.

Q2 — B. ->> returns text; -> returns jsonb.

Q3 — B. @> is containment ("does left contain right?"), and it's indexable with GIN.

Q4 — B. GIN indexes accelerate JSONB containment and full-text search.

Q5 — B. @@ matches a tsvector against a tsquery.

Q6 — B. It normalizes into lexemes (stemming, stop words, lowercasing).

Q7 — B. Stemming and relevance ranking (plus GIN-index speed and boolean queries).

Q8 — B. Small, mostly-whole lists. Heavily-queried/constrained values belong in a junction table.

Q9 — B. One row per interval — a date spine for "per period" reports.

Q10 — B. One capable database often replaces a separate document/search store.

Q11 — False. JSONB complements columns for variable data; modeling relational data as documents is the Lumen mistake (Ch. 1). Design still rules.

Q12 — True. Without a GIN (JSONB/FTS) or GiST (ranges) index, these queries fall back to full scans.

Q13. PostgreSQL's jsonb already provides flexible, schema-less attributes inside the relational database (with @> queries and GIN indexing), so you keep transactions, joins, foreign keys, and one system to operate. Adding MongoDB means another system to deploy/secure/back up/keep consistent, for a need jsonb covers. Reach for a separate database only when there's a concrete requirement PostgreSQL genuinely can't meet (Part VI / Chapter 37).

Scoring: 11–13 you've finished SQL Mastery; 8–10 review JSONB operators and FTS; below 8, redo Exercises A–B.