Skip to content
Tutorials Center
My learning

Topics

Lesson 84 of 90 ยท Projects

Project: Product Catalog with JSONB and Search

Build a flexible product catalog in PostgreSQL: JSONB attributes, containment filters, GIN indexes, facet counts and ranked full-text search.

Every shop has the same modeling headache. A laptop has RAM, a CPU and a screen size. A shoe has sizes and a color. A book has an author and a page count. If you give each kind of product its own table, you maintain dozens of tables and every new category needs a migration. If you cram every possible property into one wide table, most columns are NULL most of the time.

PostgreSQL offers a middle path: keep the properties every product has (SKU, name, price, category) as normal columns, and put the category-specific properties in one jsonb column. In this project you build that catalog, filter it, compute facets (the "Brand: Acme (2)" counts beside search results), and rank text search results. It builds on JSON and JSONB and JSONB in Practice.

What runs live. SQLite has JSON functions (json_extract, json_set, json_each, json_valid) that do the same jobs as PostgreSQL's operators, so the filters, facets and updates run live with SQLite spellings. The native PostgreSQL syntax (->>, @>, jsonb_path_ops GIN indexes, tsvector ranking) is shown in read-only blocks, each next to its live counterpart.

The design

Hybrid schema

Columns SKU, name, price, category. Always present.
JSONB Attributes that differ by category.
Search column A generated tsvector for ranked text search.

Relational where the shape is stable, JSON where it varies.

1 / 2

Here is the PostgreSQL schema. Note the CHECK that forces attrs to be a JSON object (not an array or a number), and the search column that PostgreSQL maintains for you:

SQL
-- PostgreSQL (read-only)
CREATE TABLE products (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         text    NOT NULL UNIQUE,
  name        text    NOT NULL,
  category    text    NOT NULL,
  price_cents integer NOT NULL CHECK (price_cents >= 0),
  description text    NOT NULL DEFAULT '',
  attrs       jsonb   NOT NULL DEFAULT '{}' CHECK (jsonb_typeof(attrs) = 'object'),

  search tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', name), 'A') ||
    setweight(to_tsvector('english', description), 'B')
  ) STORED
);

CREATE INDEX products_attrs_gin   ON products USING gin (attrs jsonb_path_ops);
CREATE INDEX products_search_gin  ON products USING gin (search);
CREATE INDEX products_brand_idx   ON products ((attrs->>'brand'));
CREATE INDEX products_category_idx ON products (category, price_cents);

Three different indexes answer three different questions. The jsonb_path_ops GIN index accelerates containment (@>) on any key. The search GIN index accelerates @@ text queries. The expression index on attrs->>'brand' serves equality and ORDER BY on one hot key, much like a normal column index. The details of these index types are in GIN and GiST Indexes.

Loading and validating attributes

The live version below uses the same shape. SQLite stores JSON as text, so the CHECK (json_valid(attrs) AND json_type(attrs) = 'object') constraint plays the role of jsonb_typeof(attrs) = 'object'. It also uses a generated column to promote the hottest attribute, brand, to a real indexed column, a technique from Generated Columns:

SQL Catalog table with JSON attributes

Press Run (or Ctrl + Enter) to see the result here.

The first query returns the Aero and the Forge. The update on LT-003 rewrites only the ram_gb key of that one document. In PostgreSQL the equivalents are jsonb_set(attrs, '{ram_gb}', '16') or the merge operator attrs || '{"ram_gb": 16}'. Remember from the earlier lessons that PostgreSQL rewrites the whole row on any update, so documents that change constantly belong in their own table.

Filtering in PostgreSQL

PostgreSQL has two ways to filter by an attribute, and the difference is about which index can help:

SQL
-- PostgreSQL (read-only)
-- 1. containment: uses the GIN index on attrs
SELECT name FROM products WHERE attrs @> '{"brand": "Acme"}';
SELECT name FROM products WHERE attrs @> '{"brand": "Stride", "color": "blue"}';
SELECT name FROM products WHERE attrs @> '{"tags": ["outdoor"]}';

-- 2. extraction and cast: needs an expression index to be fast
SELECT name FROM products WHERE (attrs->>'ram_gb')::int >= 16;
CREATE INDEX products_ram_idx ON products (((attrs->>'ram_gb')::int));

-- 3. key existence and jsonpath
SELECT name FROM products WHERE attrs ? 'sizes';
SELECT name FROM products WHERE attrs @@ '$.sizes[*] == 42';

Containment (@>) is the workhorse for equality filters, and it also handles nested objects and array membership: {"tags": ["outdoor"]} matches any product whose tags array contains outdoor. Numeric ranges such as "16 GB or more" cannot use a GIN index, because GIN indexes only support containment and existence. For those, either create an expression index on the cast value, as above, or promote the attribute to a real column. Note that ->> returns text, so the cast ::int is required, and a row where ram_gb holds a non-number makes the cast fail for the whole query. Validate attribute types on write (a CHECK using jsonb_typeof, or a JSON Schema validated in the application) to avoid that.

Array attributes are searched in SQLite with json_each, which turns the array into rows:

SQL Filter and count by array attributes

Press Run (or Ctrl + Enter) to see the result here.

Two products are tagged outdoor: the Summit Boot and the Trail Runner Shoe. The size-42 query returns the same two shoes. The tag count shows outdoor and portable with two products each; every other tag appears once. In PostgreSQL the counting query uses jsonb_array_elements_text(attrs->'tags') in the FROM clause.

Facets and aggregates

A faceted search page shows counts next to each filter value, computed over the current result set. Facets are plain GROUP BY queries over an extracted key, and aggregates such as average price work like they do on any table:

SQL
-- PostgreSQL (read-only)
SELECT attrs->>'brand' AS brand, count(*) AS products,
       round(avg(price_cents) / 100.0, 2) AS avg_price
FROM products
WHERE category = 'laptop'
GROUP BY attrs->>'brand'
ORDER BY products DESC;

-- one query for several facets at once
SELECT jsonb_object_agg(brand, n) AS brands
FROM (SELECT attrs->>'brand' AS brand, count(*) AS n
      FROM products WHERE category = 'laptop' GROUP BY 1) s;

The live version runs both a facet and a price summary per category. The lowest, highest and average prices are extracted with ordinary aggregates, and json_group_object (the SQLite spelling of jsonb_object_agg) packs a facet into one JSON value your API could return directly:

SQL Facets and aggregates over JSON attributes

Press Run (or Ctrl + Enter) to see the result here.

Acme has two laptops (average price 1,049.00) and Forge has one. The packed object lists all three brands with their counts, ready for a JSON API response. Facet queries over a very large catalog can be slow because they scan the filtered rows; the usual remedies are the expression index on the facet key, a materialized view refreshed periodically, or a dedicated search engine once you need dozens of live facets.

Search with ranking

Users type words, and the best match should come first. PostgreSQL does this with tsvector and tsquery (see Full-Text Search). With the generated search column above, a ranked query looks like this:

SQL
-- PostgreSQL (read-only)
SELECT name,
       ts_rank(search, query) AS rank
FROM products,
     websearch_to_tsquery('english', 'light laptop for travel') AS query
WHERE search @@ query
ORDER BY rank DESC, price_cents
LIMIT 20;

setweight made name matches (A) count more than description matches (B), websearch_to_tsquery accepts what a user would type in a search box (quotes, or, -word), and english stemming means "laptops" matches "laptop". Full-text search can be combined with attribute filters in the same WHERE, for example AND attrs @> '{"brand": "Acme"}', and PostgreSQL will use whichever indexes help.

SQLite's full-text module is not part of this site's build, so the live stand-in scores results by hand: one point per search word found in the description, three per word found in the name. It is crude (no stemming, no stop words) but shows the idea of weighted ranking:

SQL A hand-made weighted search ranking

Press Run (or Ctrl + Enter) to see the result here.

The Forge laptop scores highest: "laptop" and "gaming" appear in its name (3 points each) and again in its description (1 point each). The Aero comes next, then the Pebble. Products that match no word are removed by HAVING score > 0. Comparing a boolean to a number works because SQLite stores comparison results as 0 or 1.

Pitfalls and when to leave JSON

  • Typos in keys. JSON has no schema, so colour and color are different keys. Validate keys in the application or a CHECK, and document the allowed attributes per category in a small table.
  • Types drift. "ram_gb": "16" (text) and 16 (number) are different values and compare differently. Cast on read or validate on write.
  • Hot attributes belong in columns. If every query filters on brand or price, make them real columns, or generated columns as shown above.
  • Large documents are slow to update. Big JSONB values are compressed and stored out of line (TOAST), and updating one key rewrites the whole value.
  • Integrity. You cannot declare a foreign key inside a document. Anything that must reference another table (such as a brand_id) should be a column.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which filter can use a GIN index created with jsonb_path_ops?

    GIN indexes on jsonb support containment and existence operators. Numeric comparisons, LIKE on extracted text and sorting need an expression index or a real column.
  2. 2. Why add CHECK (jsonb_typeof(attrs) = 'object') to the table?

    The check only guarantees the top-level shape. Type checks for individual keys need extra constraints or application validation.
  3. 3. In the weighted search, why does setweight(to_tsvector('english', name), 'A') matter?

    Weights A to D feed into ts_rank, so a word found in the name (A) contributes more than the same word found in the description (B).
  4. 4. When should a JSON attribute become a real column?

    Frequently queried or constrained attributes are better as columns or generated columns. JSON is for properties that genuinely vary and are only read in bulk.

Summary

  • Use a hybrid schema: stable fields as columns, variable attributes in one jsonb object guarded by a CHECK.
  • Filter equality and array membership with containment (@>) and a jsonb_path_ops GIN index; use expression indexes or real columns for numeric ranges and sorting.
  • Facets are GROUP BY on an extracted key; jsonb_object_agg can pack them into one value.
  • A generated tsvector column with setweight plus websearch_to_tsquery and ts_rank gives ranked search.
  • Validate keys and types on write, promote hot attributes to columns, and keep frequently updated data out of big documents.

Next: Project: Audit Log with Triggers.