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: ...