How to Work with JSON in PostgreSQL

How do I store, query, and index JSON data in PostgreSQL?

PostgreSQL’s JSON data types and functions let you store, query, and transform JSON documents directly in SQL.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What are PostgreSQL’s JSON data types?

PostgreSQL offers two JSON-capable types—json and jsonb. json stores text exactly as inserted. jsonb stores a decomposed binary representation that supports indexing and faster querying. Choose jsonb for most workloads.

When should I pick json vs jsonb?

Pick json when you only need to validate that text is valid JSON and want the original whitespace.Pick jsonb when you intend to query, index, or update individual keys.

How do I create a table with a JSON column?

Use CREATE TABLE with a json or jsonb column. Add GIN indexes for jsonb to accelerate key lookups.

What is the basic syntax for querying JSON?

Use -> to get JSON objects, ->> to get text, #> to drill into paths, and ? for existence checks.Combine with WHERE and indexes.

How do I index JSON fields for speed?

For jsonb columns, create a GIN index: CREATE INDEX idx ON Orders USING gin (payload jsonb_path_ops);. Functional indexes accelerate specific keys.

How can I update a single JSON key?

Use jsonb_set or the || operator.Example: UPDATE Customers SET prefs = jsonb_set(prefs,'{theme}','"dark"');.

Can I aggregate rows into JSON?

json_agg and jsonb_build_object let you bundle result sets into JSON documents, ideal for APIs.

Example: return an order with its items

SELECT o.id, json_agg(jsonb_build_object('product',p.name,'qty',oi.quantity)) AS items FROM Orders o JOIN OrderItems oi ON oi.order_id=o.id JOIN Products p ON p.id=oi.product_id WHERE o.id=42 GROUP BY o.id;

Best practices for JSON in PostgreSQL

1. Prefer jsonb with GIN indexes. 2. Keep frequently filtered keys in regular columns. 3. Use CHECK constraints for schema control.

Why How to Work with JSON in PostgreSQL is important

How to Work with JSON in PostgreSQL Example Usage


-- Find orders where payload->'shipping'->>'state' = 'CA'
SELECT id, total_amount
FROM Orders
WHERE payload #>> '{shipping,state}' = 'CA';

How to Work with JSON in PostgreSQL Syntax


-- Creating a table with jsonb and indexes
CREATE TABLE Orders (
    id              SERIAL PRIMARY KEY,
    customer_id     INT REFERENCES Customers(id),
    order_date      DATE,
    total_amount    NUMERIC(10,2),
    payload         JSONB
);
-- GIN index for key searches
CREATE INDEX idx_orders_payload ON Orders USING gin (payload);

-- Query operators
column -> 'key'            -- JSON object
column ->> 'key'           -- text value
column #> '{path,subkey}'  -- nested JSON
column ? 'key'             -- key existence

-- Functions
jsonb_set(target, path, value, [create_missing])
jsonb_build_object(k1,v1,...)
json_agg(expression)

Common Mistakes

Mistake: Using json instead of jsonb for query-heavy workloads. json cannot be indexed efficiently, causing slow lookups. Fix: switch column type to jsonb and add a GIN index.
Mistake: Forgetting to cast ->> results. ->> returns text, so numeric comparisons fail. Fix: CAST( payload->>'price' AS NUMERIC ) when filtering numbers.