-- Create table with JSONB column
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50),
profile JSONB,
preferences JSONB,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Insert JSON data
INSERT INTO users (username, profile, preferences) VALUES
(
'alice',
'{"age": 25, "city": "New York", "skills": ["SQL", "Python", "JavaScript"]}',
'{"theme": "dark", "notifications": true, "language": "en"}'
);
-- Insert with jsonb_build_object
INSERT INTO users (username, profile) VALUES
(
'bob',
jsonb_build_object(
'age', 30,
'city', 'San Francisco',
'skills', jsonb_build_array('Ruby', 'Go'),
'contact', jsonb_build_object('email', 'bob@example.com', 'phone', '555-0100')
)
);
-- Extract JSON field (returns JSON)
SELECT
username,
profile -> 'city' AS city_json,
profile ->> 'city' AS city_text
FROM users;
-- -> returns JSON, ->> returns text
-- Extract nested field
SELECT
username,
profile -> 'contact' ->> 'email' AS email,
profile #> '{contact,email}' AS email_path
FROM users;
-- Filter by JSON field
SELECT username
FROM users
WHERE profile ->> 'city' = 'New York';
-- Check key existence
SELECT username
FROM users
WHERE profile ? 'age'; -- Has 'age' key
-- Check multiple keys
SELECT username
FROM users
WHERE profile ?& ARRAY['age', 'city']; -- Has all keys
SELECT username
FROM users
WHERE profile ?| ARRAY['age', 'location']; -- Has any key
-- Containment (@> contains, <@ contained by)
SELECT username
FROM users
WHERE profile @> '{"city": "New York"}';
-- Find users with specific skill
SELECT username
FROM users
WHERE profile @> '{"skills": ["SQL"]}';
-- JSON array operations
SELECT
username,
jsonb_array_length(profile -> 'skills') AS skill_count,
jsonb_array_elements_text(profile -> 'skills') AS skill
FROM users;
-- Update JSON field
UPDATE users
SET profile = profile || '{"verified": true}'
WHERE username = 'alice';
-- Update nested field
UPDATE users
SET profile = jsonb_set(
profile,
'{contact,phone}',
'"555-0200"'
)
WHERE username = 'bob';
-- Remove field
UPDATE users
SET profile = profile - 'temporary_field'
WHERE id = 1;
-- Remove nested field
UPDATE users
SET profile = profile #- '{contact,phone}'
WHERE id = 2;
-- GIN index for JSONB
CREATE INDEX idx_users_profile_gin ON users USING GIN (profile);
-- Now containment queries use index
EXPLAIN ANALYZE
SELECT * FROM users
WHERE profile @> '{"city": "New York"}';
-- Index specific JSON path
CREATE INDEX idx_users_profile_city
ON users ((profile ->> 'city'));
-- Expression index on nested field
CREATE INDEX idx_users_email
ON users ((profile -> 'contact' ->> 'email'));
-- JSON path queries (PostgreSQL 12+)
SELECT username, profile
FROM users
WHERE profile @? '$.skills[*] ? (@ == "SQL")';
-- jsonb_path_query
SELECT
username,
jsonb_path_query(profile, '$.skills[*]') AS skill
FROM users;
-- JSON aggregation
SELECT
profile ->> 'city' AS city,
jsonb_agg(
jsonb_build_object(
'username', username,
'age', profile ->> 'age'
)
) AS users
FROM users
GROUP BY profile ->> 'city';
-- JSON object aggregation
SELECT
jsonb_object_agg(
username,
profile -> 'age'
) AS user_ages
FROM users;
-- Pretty print JSON
SELECT jsonb_pretty(profile) FROM users LIMIT 1;
-- Get all keys
SELECT DISTINCT jsonb_object_keys(profile) AS key
FROM users;
-- Get all values
SELECT
username,
key,
value
FROM users,
jsonb_each(profile)
WHERE key IN ('age', 'city');
-- Expand JSON to rows
SELECT
u.username,
e.key,
e.value
FROM users u
CROSS JOIN LATERAL jsonb_each_text(u.profile) e;
-- Array to rows
SELECT
u.username,
skill
FROM users u
CROSS JOIN LATERAL jsonb_array_elements_text(u.profile -> 'skills') skill;
-- Merge JSONB objects
SELECT
profile || preferences AS merged
FROM users
LIMIT 1;
-- Deep merge
SELECT
jsonb_set(
profile,
'{contact}',
COALESCE(profile -> 'contact', '{}'::jsonb) ||
'{"twitter": "@user"}'::jsonb
) AS updated_profile
FROM users;
-- Conditional update
UPDATE users
SET preferences = CASE
WHEN preferences ? 'theme'
THEN jsonb_set(preferences, '{theme}', '"light"')
ELSE preferences || '{"theme": "light"}'
END;
-- Type checking
SELECT
username,
jsonb_typeof(profile -> 'age') AS age_type,
jsonb_typeof(profile -> 'skills') AS skills_type
FROM users;
-- Validate JSON structure
CREATE OR REPLACE FUNCTION validate_user_profile(data JSONB)
RETURNS BOOLEAN AS $$
BEGIN
RETURN (
data ? 'age' AND
jsonb_typeof(data -> 'age') = 'number' AND
(data ->> 'age')::INT BETWEEN 0 AND 150 AND
data ? 'city' AND
jsonb_typeof(data -> 'city') = 'string'
);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- Add constraint
ALTER TABLE users
ADD CONSTRAINT valid_profile
CHECK (validate_user_profile(profile));
-- Full-text search on JSON
CREATE INDEX idx_users_profile_fts
ON users
USING GIN (to_tsvector('english', profile::text));
SELECT username
FROM users
WHERE to_tsvector('english', profile::text) @@
to_tsquery('english', 'Python & SQL');
-- Event store pattern
CREATE TABLE events (
id SERIAL PRIMARY KEY,
event_type VARCHAR(50) NOT NULL,
payload JSONB NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_events_type ON events (event_type);
CREATE INDEX idx_events_payload_gin ON events USING GIN (payload);
INSERT INTO events (event_type, payload) VALUES
(
'user_registered',
'{"user_id": 123, "email": "user@example.com", "source": "web"}'
),
(
'order_placed',
'{"order_id": 456, "user_id": 123, "total": 99.99, "items": [{"id": 1, "qty": 2}]}'
);
-- Query events
SELECT *
FROM events
WHERE event_type = 'order_placed'
AND (payload ->> 'total')::DECIMAL > 50;
-- Schemaless tables with JSONB
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
type VARCHAR(50) NOT NULL,
data JSONB NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_documents_type ON documents (type);
CREATE INDEX idx_documents_data_gin ON documents USING GIN (data);
-- Different document types in same table
INSERT INTO documents (type, data) VALUES
('product', '{"name": "Laptop", "price": 999, "specs": {"cpu": "i7", "ram": "16GB"}}'),
('article', '{"title": "DB Guide", "author": "Maria", "content": "...", "tags": ["db", "sql"]}');
-- Type-specific queries
SELECT data ->> 'name' AS product_name
FROM documents
WHERE type = 'product'
AND (data ->> 'price')::DECIMAL < 1000;
SELECT data ->> 'title' AS article_title
FROM documents
WHERE type = 'article'
AND data -> 'tags' @> '["sql"]';
