postgresql jsonb json nosql schema-design indexing

PostgreSQL JSONB for flexible schema design

Maria Garcia Feb 2026

2 tabs

-- Create table with JSONB column
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255),
  metadata JSONB DEFAULT '{}'::jsonb
);

-- Insert JSONB data
INSERT INTO users (email, name, metadata) VALUES
  ('alice@example.com', 'Alice',
   '{"age": 30, "city": "NYC", "premium": true, "preferences": {"theme": "dark"}}'::jsonb),
  ('bob@example.com', 'Bob',
   '{"age": 25, "city": "SF", "premium": false}'::jsonb);

-- Query JSONB: -> returns JSON, ->> returns text
SELECT
  name,
  metadata -> 'city' as city_json,
  metadata ->> 'city' as city_text,
  metadata -> 'preferences' ->> 'theme' as theme
FROM users;

-- Check if key exists
SELECT name
FROM users
WHERE metadata ? 'premium';

-- Check if value exists in array
SELECT name
FROM users
WHERE metadata -> 'tags' ? 'developer';

-- Contains operator (@>)
SELECT name
FROM users
WHERE metadata @> '{"premium": true}'::jsonb;

-- Containment check
SELECT name
FROM users
WHERE metadata @> '{"city": "NYC", "age": 30}'::jsonb;

-- Extract nested values
SELECT
  name,
  metadata #> '{preferences, theme}' as theme,
  metadata #>> '{preferences, notifications, email}' as email_notif
FROM users;

-- Update JSONB field
UPDATE users
SET metadata = metadata || '{"last_login": "2024-01-15"}'::jsonb
WHERE email = 'alice@example.com';

-- Set specific key
UPDATE users
SET metadata = jsonb_set(
  metadata,
  '{preferences, language}',
  '"en"'::jsonb
)
WHERE id = 1;

-- Remove key
UPDATE users
SET metadata = metadata - 'temporary_field';

-- Array operations
UPDATE users
SET metadata = jsonb_set(
  metadata,
  '{tags}',
  (COALESCE(metadata -> 'tags', '[]'::jsonb) || '"new-tag"'::jsonb)
);

-- Create GIN index for JSONB
CREATE INDEX idx_users_metadata ON users USING GIN (metadata);

-- Index specific JSONB path
CREATE INDEX idx_users_metadata_city
  ON users ((metadata ->> 'city'));

-- Index for containment queries
CREATE INDEX idx_users_metadata_path
  ON users USING GIN (metadata jsonb_path_ops);

-- Aggregate JSONB
SELECT
  metadata ->> 'city' as city,
  COUNT(*) as user_count,
  jsonb_agg(name) as user_names
FROM users
GROUP BY metadata ->> 'city';

-- Build JSONB object from query
SELECT
  jsonb_build_object(
    'user_id', id,
    'email', email,
    'premium', metadata -> 'premium',
    'city', metadata ->> 'city'
  ) as user_summary
FROM users;

-- Expand JSONB to rows
SELECT
  u.name,
  jsonb_each_text(u.metadata)
FROM users u;

-- Array elements to rows
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  data JSONB
);

SELECT
  id,
  jsonb_array_elements(data -> 'participants') as participant
FROM events;

-- Complex filtering
SELECT name
FROM users
WHERE
  (metadata ->> 'age')::int > 25
  AND metadata @> '{"premium": true}'::jsonb
  AND metadata -> 'preferences' ->> 'theme' = 'dark';

-- Generated column for JSONB field (PostgreSQL 12+)
ALTER TABLE users
  ADD COLUMN city VARCHAR(100)
  GENERATED ALWAYS AS (metadata ->> 'city') STORED;

CREATE INDEX idx_users_city ON users(city);

-- Now can query efficiently:
SELECT * FROM users WHERE city = 'NYC';

-- Validate JSONB structure with CHECK constraint
ALTER TABLE users
  ADD CONSTRAINT check_metadata_structure
  CHECK (
    metadata ? 'age' AND
    jsonb_typeof(metadata -> 'age') = 'number'
  );

-- Deep merge JSONB
CREATE OR REPLACE FUNCTION jsonb_merge(a jsonb, b jsonb)
RETURNS jsonb LANGUAGE sql AS $$
  SELECT jsonb_object_agg(key, value)
  FROM (
    SELECT key, value FROM jsonb_each(a)
    UNION ALL
    SELECT key, value FROM jsonb_each(b)
  ) t
$$;

-- Full-text search on JSONB
CREATE INDEX idx_users_metadata_fts
  ON users
  USING GIN ((metadata ->> 'bio') gin_trgm_ops);

SELECT name
FROM users
WHERE metadata ->> 'bio' ILIKE '%developer%';

-- JSONB statistics
SELECT
  COUNT(*) as total_users,
  COUNT(metadata -> 'premium') as has_premium_field,
  COUNT(*) FILTER (WHERE metadata @> '{"premium": true}') as premium_users,
  AVG((metadata ->> 'age')::int) as avg_age
FROM users
WHERE metadata ? 'age';

2 files · sql Explain with highlit

PostgreSQL JSONB stores binary JSON efficiently with indexing support. I use JSONB for semi-structured data, dynamic attributes, event logs. JSONB operators enable querying nested data—->, ->>, @>, ?. GIN indexes accelerate JSONB queries. JSONB avoids EAV anti-pattern while maintaining flexibility. Indexing specific JSONB paths optimizes common queries. JSONB outperforms JSON—binary format, indexable. Use JSONB for polymorphic associations, metadata, user preferences. Understanding when to use JSONB versus normalized tables balances flexibility and performance. JSONB enables schema evolution without migrations. Generated columns extract JSONB fields for traditional indexing. JSONB is PostgreSQL's secret weapon for flexible data modeling.