JSONB and Other Special Types

PostgreSQL offers two JSON storage types: JSON (stores text verbatim, validates on insert) and JSONB (stores a decomposed binary format that is faster to query and supports indexing). JSONB is almost always the better choice. PostgreSQL provides a rich set of operators for querying JSONB: the arrow operator -> returns a JSON value by key, ->> returns it as text, #> navigates a path, and @> tests containment. GIN indexes on JSONB columns make containment and existence queries very fast. PostgreSQL also supports HSTORE (key-value pairs), INET and CIDR (network addresses), POINT/LINE/POLYGON (geometric types), and the tsvector/tsquery types for full-text search.

Example

-- JSONB column
CREATE TABLE products (
  id       SERIAL PRIMARY KEY,
  name     TEXT NOT NULL,
  metadata JSONB
);

INSERT INTO products (name, metadata) VALUES
  ('Laptop', '{"brand":"Dell","specs":{"ram":16,"ssd":512},"tags":["work","portable"]}'),
  ('Phone',  '{"brand":"Apple","specs":{"ram":8,"storage":256},"tags":["mobile"]}');

-- Arrow operator: -> returns JSONB, ->> returns TEXT
SELECT
  name,
  metadata -> 'brand'           AS brand_json,    -- "Dell"
  metadata ->> 'brand'          AS brand_text,    -- Dell
  metadata -> 'specs' ->> 'ram' AS ram            -- 16
FROM products;

-- Path operator
SELECT metadata #>> '{specs,ssd}' AS ssd FROM products;

-- Containment: find products with tag 'mobile'
SELECT * FROM products WHERE metadata @> '{"tags": ["mobile"]}';

-- Key existence
SELECT * FROM products WHERE metadata ? 'brand';

-- Update a JSONB field
UPDATE products
  SET metadata = jsonb_set(metadata, '{specs,ram}', '32')
WHERE name = 'Laptop';

-- GIN index for fast JSONB queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned