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:
| Expression | Returns |
|---|---|
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 -> 0 | First element of an array |
(doc ->> 'count')::int | Cast 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
| Task | SQL | ||
|---|---|---|---|
| Text value | doc ->> 'key' | ||
| Nested value | doc #>> '{a,b}' | ||
| Contains | doc @> '{"k": "v"}' | ||
| Key exists | doc ? 'k' | ||
| Set a value | jsonb_set(doc, '{a,b}', 'true') | ||
| Merge / remove | `doc | ‘{…}’, doc – ‘k’` | |
| JSON to rows | JSON_TABLE(...), jsonb_array_elements | ||
| Index documents | USING gin (doc jsonb_path_ops) | ||
| Text to lexemes | to_tsvector('english', text) | ||
| User query | websearch_to_tsquery('english', q) | ||
| Match / rank | search @@ q, ts_rank(search, q) | ||
| Index search | USING gin (search) |
Exercise
Which expression returns Ana’s theme as text, ready to compare with ‘dark’?
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?
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?
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
jsonbstores flexible documents; read with->and->>, filter with@>, change withjsonb_setand||.JSON_TABLEturns 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_tsqueryfor user input. - A stored generated
tsvectorcolumn 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.