Advanced20 min read

JSON & Advanced Types

Work with JSONB, arrays, composite types, and domain-specific PostgreSQL types.

JSON and JSONB

JSON stores exact text; JSONB stores parsed binary format optimized for indexing and containment. Prefer JSONB for almost all application use.

Operators include ->, ->>, #>, @>, ? (key exists), and jsonb_path_query for SQL/JSON path expressions.

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

INSERT INTO events (payload) VALUES
  ('{"type": "click", "meta": {"page": "/home"}}'::jsonb);

SELECT payload->>'type' AS event_type FROM events
WHERE payload @> '{"type": "click"}';

JSONB Indexing

GIN indexes accelerate @>, ?, ?&, ?| operators. jsonb_path_ops variant smaller for containment-only workloads. Expression indexes on -> path for equality filters.

Balance document size—large JSONB rows increase I/O and vacuum cost.

CREATE INDEX idx_events_payload ON events USING GIN (payload jsonb_path_ops);

Array Type

PostgreSQL arrays are typed (TEXT[], INT[]). Use ANY, ALL, unnest for queries. Arrays suit ordered lists of primitives; junction tables suit relational many-to-many with attributes.

Multidimensional arrays exist but rare in application schema.

CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  tags TEXT[] NOT NULL DEFAULT '{}'
);

SELECT * FROM posts WHERE 'postgresql' = ANY(tags);

Composite and Custom Types

CREATE TYPE defines composite structures used as column types or function parameters. Domains add constraints to base types (e.g., email domain over TEXT).

Composite types reduce join count for tightly coupled value objects but complicate ORM mapping.

CREATE TYPE address AS (
  street TEXT,
  city TEXT,
  postal_code TEXT
);

CREATE TABLE customers (
  id BIGSERIAL PRIMARY KEY,
  home address
);

Other Advanced Types

Range types (daterange, numrange) model intervals with overlap operators. UUID, INET, MACADDR, geometric types serve specialized domains. hstore (extension) predates JSONB for key-value but JSONB largely supersedes it.

Choose types matching query patterns—rich types enable expressive SQL constraints and indexes.