JSON in PostgreSQL — JSONB Columns & Indexing

Storing & Querying JSON in PostgreSQL with JSONB

Complete guide to PostgreSQL JSONB: store JSON data, query with -> and ->> operators, add GIN indexes, and convert JSON into typed SQL columns.

PostgreSQL supports native JSON storage via the JSONB column type. Use the -> operator to extract objects and ->> to extract text values. GIN indexes make JSONB queries as fast as regular column queries.

PostgreSQL JSONB: Native JSON in a Relational Database

PostgreSQL's JSONB type stores JSON in a decomposed binary format that supports indexing, querying with operators, and full SQL JOIN capabilities.

JSONB vs JSON Type

JSONB (recommended): Binary storage, supports indexing, slightly slower writes.
JSON: Text storage, preserves whitespace and key order, no indexing.

Key Operators

-> 'key' — Extract JSON object field (returns JSON).
->> 'key' — Extract JSON field as text (returns TEXT).
#> '{a,b}' — Extract nested path (returns JSON).
@> '{}' — Contains operator (supports GIN index).

json

-- Create table with JSONB column
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert JSON data
INSERT INTO orders (customer_id, payload)
VALUES (101, '{"product":"Widget A","qty":5,"price":49.99,"tags":["sale"]}');

-- Query: extract specific field as text
SELECT payload->>'product' AS product_name FROM orders;

-- Query: filter by nested JSON value
SELECT * FROM orders WHERE payload->>'product' = 'Widget A';

-- Query: contains operator (use GIN index for performance)
SELECT * FROM orders WHERE payload @> '{"tags":["sale"]}';

-- Create GIN index for fast JSONB queries
CREATE INDEX idx_orders_payload ON orders USING GIN (payload);

Frequently Asked Questions

What is the difference between JSONB and JSON in PostgreSQL?

JSONB stores JSON in decomposed binary form with support for indexing and operators. JSON stores raw text with exact whitespace preserved but no index support.

How do I index a JSONB column in PostgreSQL?

Use CREATE INDEX idx_name ON table USING GIN (jsonb_column) for full-document querying or a partial index for specific key queries.

How do I convert a JSON API response to a PostgreSQL INSERT?

Use our free JSON to SQL generator to automatically create CREATE TABLE and INSERT statements from your JSON data.