Database Tutorial #5: PostgreSQL JSON and Full-Text Search

PostgreSQL is not just a relational database. It has first-class JSON support and a powerful full-text search engine built in. No separate search service needed for most applications.

jsonb vs json

PostgreSQL has two JSON types. Always use jsonb.

	`json`	`jsonb`
Storage	Text, preserves whitespace	Binary, compressed
Indexing	Not indexable	GIN index supported
Query speed	Slow (re-parses on each read)	Fast
Key order	Preserved	Not preserved

CREATE TABLE products (
  id        SERIAL PRIMARY KEY,
  name      TEXT NOT NULL,
  metadata  JSONB
);

INSERT INTO products (name, metadata) VALUES
  ('Laptop', '{"brand": "Dell", "specs": {"ram": 16, "ssd": 512}, "tags": ["electronics", "work"]}'),
  ('Phone',  '{"brand": "Apple", "specs": {"ram": 8, "ssd": 256}, "tags": ["electronics", "mobile"]}');

jsonb Query Operators

-- -> returns a JSON value (keeps JSON type)
SELECT metadata -> 'brand' FROM products;
-- "Dell"

-- ->> returns text
SELECT metadata ->> 'brand' FROM products;
-- Dell

-- #> for nested paths (returns JSON)
SELECT metadata #> '{specs, ram}' FROM products;
-- 16

-- #>> for nested paths (returns text)
SELECT metadata #>> '{specs, ram}' FROM products;
-- 16

-- @> containment: does the left side contain the right?
SELECT * FROM products WHERE metadata @> '{"brand": "Dell"}';

-- ? key exists
SELECT * FROM products WHERE metadata ? 'brand';

-- ?| any of these keys exist
SELECT * FROM products WHERE metadata ?| ARRAY['brand', 'price'];

-- ?& all of these keys exist
SELECT * FROM products WHERE metadata ?& ARRAY['brand', 'specs'];

Updating jsonb

-- Replace a key
UPDATE products
SET metadata = jsonb_set(metadata, '{brand}', '"Lenovo"')
WHERE id = 1;

-- Add a new key
UPDATE products
SET metadata = metadata || '{"price": 999}'
WHERE id = 1;

-- Remove a key
UPDATE products
SET metadata = metadata - 'price'
WHERE id = 1;

-- Update nested value
UPDATE products
SET metadata = jsonb_set(metadata, '{specs, ram}', '32')
WHERE id = 1;

Indexing jsonb

A GIN index makes containment queries (@>) and key existence (?) fast:

-- Index the whole jsonb column
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- Now these are fast:
SELECT * FROM products WHERE metadata @> '{"brand": "Dell"}';
SELECT * FROM products WHERE metadata ? 'price';

For path-specific queries, a functional index is more efficient:

-- Index just one field
CREATE INDEX idx_products_brand ON products ((metadata ->> 'brand'));

-- Fast for equality
SELECT * FROM products WHERE metadata ->> 'brand' = 'Dell';

jsonb_path_query (SQL/JSON Path)

PostgreSQL 12+ supports SQL/JSON path language:

-- Find products where RAM > 12
SELECT * FROM products
WHERE jsonb_path_exists(metadata, '$.specs.ram ? (@ > 12)');

-- Extract values with path
SELECT jsonb_path_query(metadata, '$.specs.ram') FROM products;

Full-Text Search

PostgreSQL full-text search uses two types:

tsvector — a processed document (words normalized to stems)
tsquery — a search query

CREATE TABLE articles (
  id      SERIAL PRIMARY KEY,
  title   TEXT NOT NULL,
  body    TEXT NOT NULL
);

INSERT INTO articles (title, body) VALUES
  ('PostgreSQL Performance', 'Indexes make queries faster. Use EXPLAIN ANALYZE.'),
  ('Getting Started', 'PostgreSQL is an open-source relational database.');

Basic search:

-- Convert text to tsvector, search with tsquery
SELECT title
FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'index & query');

Storing tsvector for Speed

Computing to_tsvector on every search is slow. Store it as a column:

ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

-- Update existing rows
UPDATE articles
SET search_vector = to_tsvector('english', title || ' ' || body);

-- Keep it in sync automatically
CREATE FUNCTION update_search_vector() RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector := to_tsvector('english', NEW.title || ' ' || NEW.body);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_search_vector_update
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION update_search_vector();

-- Index the vector column
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

Now search is fast:

SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'index') query
WHERE search_vector @@ query
ORDER BY rank DESC;

Highlighting Search Results

SELECT
  title,
  ts_headline('english', body, to_tsquery('english', 'index'),
    'MaxWords=15, MinWords=10, StartSel=<b>, StopSel=</b>'
  ) AS snippet
FROM articles
WHERE search_vector @@ to_tsquery('english', 'index');

Output:

PostgreSQL Performance | <b>Indexes</b> make queries faster. Use EXPLAIN ANALYZE.

Python Example

import psycopg
import json

conn = psycopg.connect("postgresql://localhost/mydb")

# Insert with jsonb
with conn.cursor() as cur:
    metadata = {"brand": "Dell", "specs": {"ram": 16, "ssd": 512}, "tags": ["electronics"]}
    cur.execute(
        "INSERT INTO products (name, metadata) VALUES (%s, %s) RETURNING id",
        ("Laptop", json.dumps(metadata))
    )
    product_id = cur.fetchone()[0]
    conn.commit()

# Query jsonb
with conn.cursor() as cur:
    cur.execute(
        "SELECT name, metadata ->> 'brand' FROM products WHERE metadata @> %s",
        (json.dumps({"brand": "Dell"}),)
    )
    for row in cur.fetchall():
        print(row)

# Full-text search
with conn.cursor() as cur:
    cur.execute(
        "SELECT title FROM articles WHERE search_vector @@ plainto_tsquery('english', %s)",
        ("postgres index performance",)
    )
    for row in cur.fetchall():
        print(row[0])

TypeScript Example

import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

// Insert with jsonb
async function createProduct(name: string, metadata: Record<string, unknown>) {
  const { rows } = await pool.query(
    "INSERT INTO products (name, metadata) VALUES ($1, $2) RETURNING id",
    [name, JSON.stringify(metadata)]
  );
  return rows[0].id;
}

// Query jsonb — containment
async function findByBrand(brand: string) {
  const { rows } = await pool.query(
    "SELECT * FROM products WHERE metadata @> $1",
    [JSON.stringify({ brand })]
  );
  return rows;
}

// Full-text search
async function searchArticles(query: string) {
  const { rows } = await pool.query(
    `SELECT title, ts_rank(search_vector, q) AS rank
     FROM articles, plainto_tsquery('english', $1) q
     WHERE search_vector @@ q
     ORDER BY rank DESC`,
    [query]
  );
  return rows;
}

What’s Next?

You now have flexible schema storage with jsonb and built-in full-text search. Next up: transactions and concurrency — how PostgreSQL keeps data consistent under load.

Next: Database Tutorial #6: PostgreSQL Transactions and Concurrency

jsonb vs json#

jsonb Query Operators#

Updating jsonb#

Indexing jsonb#

jsonb_path_query (SQL/JSON Path)#

Full-Text Search#

Storing tsvector for Speed#

Highlighting Search Results#

Python Example#

TypeScript Example#

What’s Next?#

Related Articles#