Part 110: JSON in SQL: Storing, Querying & Indexing JSONB Data

Modern relational databases support hybrid relational + document workloads.

1. PostgreSQL JSONB (Binary JSON)

CREATE TABLE audit_events (
  id BIGSERIAL PRIMARY KEY,
  payload JSONB NOT NULL
);

-- Querying inside JSON using operators:
-- -> returns JSON object, ->> returns text
SELECT payload->>'user_id' AS user_id, payload->'metadata'->>'browser' AS browser
FROM audit_events
WHERE payload->>'action' = 'login_failed';

2. GIN Indexing on JSONB (Lightning Fast JSON Search)

CREATE INDEX idx_audit_payload ON audit_events USING GIN (payload);

Key Takeaways

• Use JSONB in PostgreSQL to store semi-structured data with B-Tree-equivalent indexing speeds.

In Part 111, we enter Module 12 to master Views: CREATE VIEW, Virtual Tables & Security!