JSONB, Arrays & Full-Text Search

JSONB operators

CREATE TABLE products (
  id    serial PRIMARY KEY,
  name  text,
  attrs jsonb
);

INSERT INTO products (name, attrs) VALUES
('Laptop', '{"brand":"Dell","ram_gb":16,"tags":["work","portable"]}');

Operator	Meaning
->	Get JSON field (returns jsonb)
->>	Get JSON field as text
#> / #>>	Get at a path (array of keys)
@> / <@	Contains / is contained by
?	Top-level key exists
?| / ?&	Any / all of these keys exist
-	Remove a key or array element
||	Concatenate / merge

SELECT attrs->'brand'         AS quoted;    -- "Dell"
SELECT attrs->>'brand'        AS unquoted;  -- Dell
SELECT attrs#>>'{tags,0}'     AS first_tag;
SELECT * FROM products WHERE attrs @> '{"brand":"Dell"}';
SELECT * FROM products WHERE attrs ? 'warranty';
SELECT * FROM products WHERE attrs->'tags' ? 'work';

Modifying JSONB

UPDATE products SET attrs = jsonb_set(attrs, '{ram_gb}', '32');
UPDATE products SET attrs = attrs || '{"color":"black"}'::jsonb;   -- merge
UPDATE products SET attrs = attrs - 'color';                        -- remove a key
UPDATE products SET attrs = attrs #- '{tags,0}';                    -- remove a path

SELECT jsonb_pretty(attrs), jsonb_object_keys(attrs) FROM products;
SELECT jsonb_array_elements(attrs->'tags') FROM products;

Building JSON from SQL

SELECT jsonb_build_object('id', id, 'name', name_en, 'cgpa', cgpa) FROM students;
SELECT jsonb_agg(jsonb_build_object('name', name_en, 'cgpa', cgpa)) FROM students;
SELECT row_to_json(s) FROM students s LIMIT 1;

A common pattern: let Postgres assemble a nested JSON response and hand it straight to an API layer.

Indexing JSONB

CREATE INDEX idx_attrs ON products USING gin (attrs);              -- supports @>, ?, ?|, ?&
CREATE INDEX idx_attrs_path ON products USING gin (attrs jsonb_path_ops);  -- smaller, only @>

-- expression index for one specific field
CREATE INDEX idx_brand ON products ((attrs->>'brand'));
SELECT * FROM products WHERE attrs->>'brand' = 'Dell';   -- uses idx_brand

jsonb_path_ops is smaller and faster but only supports the @> operator — use it when that is the only query pattern you need.

Arrays

CREATE TABLE posts (id serial PRIMARY KEY, title text, tags text[]);
INSERT INTO posts (title, tags) VALUES ('Intro', ARRAY['sql','beginner']);

SELECT * FROM posts WHERE 'sql' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['sql'];          -- contains
SELECT * FROM posts WHERE tags && ARRAY['sql','db'];     -- overlaps
SELECT array_length(tags, 1), array_append(tags, 'new') FROM posts;
SELECT unnest(tags) FROM posts;                           -- one row per element

CREATE INDEX idx_tags ON posts USING gin (tags);

Full-text search

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

CREATE INDEX idx_search ON articles USING gin (search_vector);

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

SELECT title FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', 'database -oracle "query optimization"');

websearch_to_tsquery parses Google-style syntax ("phrase", -exclude, OR) directly from user input, which to_tsquery does not.

For fuzzy or typo-tolerant search, combine with pg_trgm:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_title_trgm ON articles USING gin (title gin_trgm_ops);
SELECT title, similarity(title, 'databse indexng') AS sim
FROM articles ORDER BY sim DESC LIMIT 5;

Postgres full-text search is genuinely production-capable for moderate scale. For very large corpora, typo-tolerant ranking or faceted search, a dedicated engine like Elasticsearch or Meilisearch is usually a better fit.