-- 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';
