Lesson 13 intermediate

JSON and Full-Text Search

Store user settings as jsonb, read nested values with -> and ->>, filter with containment, update documents with jsonb_set, turn JSON into rows with JSON_TABLE, index documents with GIN; then add real full-text search to Orbit's notes with tsvector, websearch queries, ranking and a generated, indexed search column.

Two features let Postgres replace extra systems in many architectures: jsonb, which stores documents you can query and index, and full-text search, which finds notes by meaning-bearing words instead of exact substrings. You add both to Orbit in this lesson.

A jsonb column for settings

User preferences change shape often: a new theme option today, notification channels tomorrow. That is a good fit for jsonb:

ALTER TABLE users ADD COLUMN settings jsonb NOT NULL DEFAULT '{}';

UPDATE users SET settings = '{"theme": "dark", "notify": {"email": true, "push": false}}'
WHERE id = 1;
UPDATE users SET settings = '{"theme": "light", "notify": {"email": false}}'
WHERE id = 2;

Chen keeps the default empty object.

Reading values

-> returns a JSON value, ->> returns text. Chain them to go deeper:

SELECT name,
       settings ->> 'theme' AS theme,
       settings -> 'notify' ->> 'email' AS email_alerts
FROM users
ORDER BY id;
    name    | theme  | email_alerts
------------+--------+--------------
 Ana Silva  | dark   | true
 Ben Carter | light  | false
 Chen Wei   | (null) | (null)
(3 rows)

A missing key returns NULL rather than an error, which is convenient but also hides typos in key names. Other accessors:

ExpressionReturns
doc -> 'key'Value as jsonb
doc ->> 'key'Value as text
doc #>> '{notify,email}'Value at a path, as text
doc['notify']['email']Subscript form (also works in UPDATE ... SET)
doc -> 0First element of an array
(doc ->> 'count')::intCast text to a real type

Filtering documents

The containment operator @> asks “does this document contain this structure?”:

SELECT name FROM users WHERE settings @> '{"theme": "dark"}';
   name
-----------
 Ana Silva
(1 row)

? tests whether a top-level key exists, and @? runs a JSON path test:

SELECT count(*) FROM users WHERE settings ? 'theme';
SELECT name FROM users WHERE settings @? '$.notify ? (@.email == true)';

Changing documents

jsonb_set replaces or adds the value at a path, and || merges objects. Turn on push notifications for Ana:

UPDATE users
SET settings = jsonb_set(settings, '{notify,push}', 'true')
WHERE id = 1
RETURNING settings;
                          settings
------------------------------------------------------------
 {"theme": "dark", "notify": {"push": true, "email": true}}
(1 row)

UPDATE 1

Notice the key order changed. jsonb stores keys sorted by length and then alphabetically, not in the order you wrote them; never rely on key order. Remove a key with settings - 'theme', or a nested one with settings #- '{notify,push}'.

From JSON to rows

jsonb_each_text turns an object into key/value rows, and jsonb_array_elements does the same for arrays. JSON_TABLE (PostgreSQL 17) maps JSON straight to typed columns, perfect for importing API payloads:

SELECT *
FROM JSON_TABLE(
  '[{"email": "dana@example.com", "plan": "pro"},
    {"email": "eli@example.com",  "plan": "free"}]'::jsonb,
  '$[*]'
  COLUMNS (email text PATH '$.email', plan text PATH '$.plan')
) AS incoming;
      email       | plan
------------------+------
 dana@example.com | pro
 eli@example.com  | free
(2 rows)

Going the other way, jsonb_build_object, to_jsonb(row) and jsonb_agg build JSON from relational data, so an API endpoint can get its whole response from one query.

Indexing jsonb

Containment and path queries can use a GIN index:

CREATE INDEX users_settings_idx ON users USING gin (settings jsonb_path_ops);

jsonb_path_ops is smaller and faster for @> and @?; the default operator class also supports the key-exists operators ?, ?| and ?&. For one key you filter on all the time, an expression B-tree index on (settings ->> 'theme') is smaller still.

Tip: If you filter, join, sort or enforce rules on a value, it deserves a real column. Keep jsonb for the parts whose shape really varies.

Full-text search

ILIKE '%backup%' finds substrings, but it does not know that “backups” and “backup” are the same word, cannot rank results and matches inside unrelated words. Full-text search works on normalised words called lexemes:

SELECT to_tsvector('english', 'Discuss the backup policy');
            to_tsvector
-----------------------------------
 'backup':3 'discuss':1 'polici':4
(1 row)

The English configuration lower-cases words, removes stop words such as “the”, and reduces words to their stem (“policy” and “policies” both become polici). The numbers are word positions, used for phrase search and ranking.

A query is a tsquery, and @@ tests whether a document matches it:

SELECT to_tsvector('english', 'Discuss the backup policy')
       @@ to_tsquery('english', 'backups & policies') AS matches;
 matches
---------
 t
(1 row)

For text typed by users, websearch_to_tsquery understands the syntax people already know from search engines: "quoted phrases", or and -excluded words. It never raises a syntax error, unlike to_tsquery.

Search Orbit’s notes

Store the searchable form in a generated column, so it is always up to date, and index it:

ALTER TABLE notes ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', body), 'B')
  ) STORED;

CREATE INDEX notes_search_idx ON notes USING gin (search);

Adding a stored column rewrites the table, which takes a few seconds for 200,000 rows. setweight marks title words as more important than body words, which ranking uses. Now search:

SELECT id, title
FROM notes
WHERE search @@ websearch_to_tsquery('english', 'database stories')
ORDER BY ts_rank(search, websearch_to_tsquery('english', 'database stories')) DESC
LIMIT 10;
 id |   title
----+------------
  3 | Book ideas
(1 row)

“database stories” matched “a database detective story” in the body: the stems databas and stori are in the document. ts_headline returns a snippet with the matched words highlighted, for showing results in the app.

Note: Use the same text search configuration ('english' here) when building the vector and the query. A different configuration produces different stems, and nothing matches.

Quick reference

TaskSQL
Text valuedoc ->> 'key'
Nested valuedoc #>> '{a,b}'
Containsdoc @> '{"k": "v"}'
Key existsdoc ? 'k'
Set a valuejsonb_set(doc, '{a,b}', 'true')
Merge / remove`doc‘{…}’, doc – ‘k’`
JSON to rowsJSON_TABLE(...), jsonb_array_elements
Index documentsUSING gin (doc jsonb_path_ops)
Text to lexemesto_tsvector('english', text)
User querywebsearch_to_tsquery('english', q)
Match / ranksearch @@ q, ts_rank(search, q)
Index searchUSING gin (search)

Exercise

Which expression returns Ana’s theme as text, ready to compare with ‘dark’?

  1. settings -> ‘theme’
  2. settings ->> ‘theme’
  3. settings ? ‘theme’
  4. settings @> ‘theme’
Show answer

Answer: B. ->> extracts the value as text. -> returns jsonb (the string with its quotes), ? only tests that the key exists, and @> tests containment.

Exercise

A search for “backups” finds a note containing “backup”, but ILIKE ‘%backups%’ does not. Why?

  1. Full-text search ignores the last letter of every word
  2. ILIKE is case-sensitive
  3. The note body is stored as jsonb
  4. Full-text search compares stemmed lexemes, so both words become backup
Show answer

Answer: D. The english configuration reduces words to their stems, so singular and plural forms match. ILIKE only compares raw substrings.

Exercise

Why is the search column declared GENERATED ALWAYS AS (…) STORED?

  1. Postgres keeps it in sync with title and body automatically, and stored values can be indexed
  2. Generated columns are the only kind a GIN index can use
  3. It makes the column take no disk space
  4. It lets the application write its own search vectors
Show answer

Answer: A. A stored generated column is recomputed on every insert and update, so it never goes stale, and its stored values can be indexed with GIN.

Key takeaways

  • jsonb stores flexible documents; read with -> and ->>, filter with @>, change with jsonb_set and ||.
  • JSON_TABLE turns JSON into typed rows; GIN indexes make document queries fast.
  • Keep values you filter, join or constrain in real columns.
  • Full-text search works on stemmed lexemes; use websearch_to_tsquery for user input.
  • A stored generated tsvector column with a GIN index gives fast, always-current search.

FAQ

jsonb or json?

jsonb in almost every case: it is faster to query, can be indexed and removes duplicate keys. json keeps the exact input text, including key order and whitespace, which matters only if you must return the original document byte for byte.

Can I enforce a structure on a jsonb column?

Yes, with CHECK constraints, for example CHECK (jsonb_typeof(settings) = 'object') or CHECK (settings ->> 'theme' IN ('dark', 'light')). For complex schemas, validate in the application as well.

Does full-text search work for other languages?

Postgres ships configurations for about thirty languages, including portuguese, spanish and german; list them with \dF. Use simple for text that should not be stemmed, such as product codes.

When should I use Elasticsearch or OpenSearch instead?

When you need advanced relevance tuning, fuzzy matching across huge corpora, faceting at scale or search across many data sources. For searching your own application data, built-in full-text search plus pg_trgm is often enough and avoids keeping two systems in sync.

How do I make search tolerate typos?

Combine full-text search with pg_trgm similarity (lesson 11): for example, suggest WHERE title % 'databse' results when the full-text query finds nothing.

↑ ↓ navigate Enter open Esc close