JSONB and Advanced Types

Introduction

JSONB stores semi-structured data inside PostgreSQL—settings, metadata, event payloads—while keeping SQL joins and transactions. This chapter covers JSONB operators, updates, GIN indexes, arrays, a full-text search preview, and useful extensions like pg_trgm and citext. Compare with MySQL JSON and document modeling in MongoDB BSON basics.

Prerequisites

Databases, Schemas, and Types
Indexes and EXPLAIN ANALYZE
Table posts with metadata JSONB (from chapter 3) or create below

JSON vs JSONB Recap

Type	Storage	Query / index
JSON	Exact text	Slower operators
JSONB	Binary, decomposed	Recommended for apps

sql

CREATE TABLE IF NOT EXISTS user_profiles (
  user_id BIGINT PRIMARY KEY REFERENCES users (id),
  settings JSONB NOT NULL DEFAULT '{}'::jsonb
);
 
INSERT INTO user_profiles (user_id, settings) VALUES
  (1, '{
    "theme": "dark",
    "notifications": {"email": true, "sms": false},
    "tags": ["sql", "postgres"]
  }'::jsonb)
ON CONFLICT (user_id) DO UPDATE SET settings = EXCLUDED.settings;

Invalid JSON fails at cast:

sql

-- ERROR: invalid input syntax for type json
-- SELECT '{not json}'::jsonb;

Extract Values: -> and ->>

sql

SELECT
  user_id,
  settings -> 'theme' AS theme_json,
  settings ->> 'theme' AS theme_text,
  settings -> 'notifications' ->> 'email' AS email_on
FROM user_profiles;

Code explanation:

-> returns JSONB (still quoted string for scalars)
->> returns text—use in WHERE comparisons
Path chaining replaces MySQL $.notifications.email

Array element:

sql

SELECT settings -> 'tags' ->> 0 AS first_tag FROM user_profiles;

Containment and Key Operators

Key exists:

sql

SELECT user_id FROM user_profiles WHERE settings ? 'theme';

Any of several keys:

sql

SELECT user_id FROM user_profiles WHERE settings ?| array['theme', 'locale'];

All keys present:

sql

SELECT user_id FROM user_profiles WHERE settings ?& array['theme', 'notifications'];

Object contains (subset):

sql

SELECT user_id
FROM user_profiles
WHERE settings @> '{"theme": "dark"}'::jsonb;

Contained by:

sql

WHERE '{"theme": "dark"}'::jsonb <@ settings;

Tag in JSON array (alternative patterns):

sql

WHERE settings -> 'tags' @> '"postgres"'::jsonb;

Compare MySQL JSON_CONTAINS—PostgreSQL @> is index-friendly with GIN.

Modify JSONB

Merge / patch:

sql

UPDATE user_profiles
SET settings = settings || '{"theme": "light", "locale": "en-US"}'::jsonb
WHERE user_id = 1;

Set nested path:

sql

UPDATE user_profiles
SET settings = jsonb_set(
  settings,
  '{notifications,email}',
  'false'::jsonb,
  true
)
WHERE user_id = 1;

Remove key:

sql

UPDATE user_profiles
SET settings = settings - 'tags'
WHERE user_id = 1;

Delete array element by index:

sql

UPDATE user_profiles
SET settings = jsonb_set(settings, '{tags,0}', 'null'::jsonb, true) - 'tags'
WHERE user_id = 1;
-- Or use jsonb_path functions in PG 12+ for complex edits

GIN Index on JSONB

Default opclass indexes all keys and values:

sql

CREATE INDEX idx_profiles_settings ON user_profiles USING GIN (settings);

Query uses index:

sql

EXPLAIN ANALYZE
SELECT user_id FROM user_profiles WHERE settings @> '{"theme": "dark"}';

Expression index on one field:

sql

CREATE INDEX idx_profiles_theme ON user_profiles ((settings ->> 'theme'));
 
SELECT user_id FROM user_profiles WHERE settings ->> 'theme' = 'dark';

Code explanation:

GIN shines for @>, ?, ?&, ?|
->> equality uses B-tree on expression index

JSONB vs Normalized vs MongoDB

Approach	Best for
Normalized tables + FKs	Relational core, reports, integrity
JSONB column	Optional fields, vendor payloads, rapid iteration
MongoDB documents	Primary document model, flexible nested arrays at scale

PostgreSQL JSONB keeps ACID with SQL—good hybrid when most data is relational.

Arrays (Advanced)

Create and query:

sql

ALTER TABLE posts ADD COLUMN IF NOT EXISTS tags TEXT[] NOT NULL DEFAULT '{}';
 
UPDATE posts SET tags = ARRAY['postgres', 'jsonb'] WHERE id = 1;
 
SELECT id, title FROM posts WHERE 'postgres' = ANY (tags);
 
SELECT id, title FROM posts WHERE tags @> ARRAY['postgres', 'jsonb'];

GIN index on arrays:

sql

CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

Aggregate to array:

sql

SELECT user_id, array_agg(title ORDER BY published_at DESC) AS titles
FROM posts
GROUP BY user_id;

Full-Text Search Preview

sql

ALTER TABLE posts ADD COLUMN IF NOT EXISTS search_vector tsvector;
 
UPDATE posts
SET search_vector = to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''));
 
CREATE INDEX idx_posts_search ON posts USING GIN (search_vector);
 
SELECT id, title
FROM posts
WHERE search_vector @@ to_tsquery('english', 'postgresql & sql');

Rank results:

sql

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

tsvector/tsquery power built-in search—pg_trgm helps fuzzy LIKE.

Extensions

Enable in database:

sql

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS citext;

Case-insensitive email column:

sql

CREATE TABLE subscribers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email CITEXT NOT NULL UNIQUE
);

Fuzzy title search:

sql

CREATE INDEX idx_posts_title_trgm ON posts USING GIN (title gin_trgm_ops);
 
SELECT id, title FROM posts WHERE title ILIKE '%postgre%';
 
SELECT id, title FROM posts WHERE title % 'postgrs';  -- similarity operator

PostGIS adds geospatial types—see official PostGIS docs for GEOGRAPHY, ST_DWithin, etc.

List extensions:

sql

\dx
SELECT * FROM pg_available_extensions WHERE name IN ('pg_trgm', 'citext', 'postgis');

FAQ

JSON or JSONB?

Default to JSONB unless you must preserve exact whitespace and key order.

Index every JSONB column?

Only if you filter on it—GIN indexes add write overhead.

JSONB vs MongoDB for nested docs?

MongoDB optimizes document storage and sharding; PostgreSQL JSONB wins when data is mostly relational with some flexible blobs.

Update deep nested key?

jsonb_set, || merge, or jsonb_path functions (PG 12+).

Why is @> slow?

Missing GIN index or large table without ANALYZE—Performance chapter.

citext vs lower(email)?

citext is cleaner for uniqueness and comparisons; expression index on lower(email) works without extension.