Store JSON in PostgreSQL

You've built a solid relational schema, and now a new feature lands on your desk: user preferences, event payloads, or a third-party API response that changes shape every release. Do you really want to add a column for every possible field? The good news is PostgreSQL has a first-class answer: store JSON data directly in a column, query inside it, and index it — without giving up the relational guarantees you rely on.

The problem this lesson solves

Relational databases shine when data is predictable: fixed columns, strict types, and clear relationships. But modern applications regularly deal with semi-structured data — a metadata field from a SaaS webhook, a settings object that varies per user, or an event payload from a message queue. Forcing that shape onto a fixed set of columns leads to a slew of ALTER TABLE statements, nullable columns, and a schema that fights you at every release.

You could store the JSON as a TEXT column and parse it in your application, but then you lose the ability to query inside that text efficiently, and you’re on the hook for validating every document's structure. You could switch to a document database, but then you lose ACID transactions, joins, and the mature tooling you already rely on. PostgreSQL offers a third path: store JSON natively as a data type, query it with SQL, and choose the storage format that fits your access patterns. This lesson walks you through why, when, and how to store JSON data in PostgreSQL so you can keep your relational soul and still handle the messy real world.

Core concept / mental model

Think of PostgreSQL's JSON support as a hybrid: you get the flexibility of a document store inside a relational database. There are two primary data types you need to understand:

• json: Stores the exact, byte-for-byte copy of the input text. It validates that the text is valid JSON, but it preserves whitespace, key order, and duplicate keys. Every query re-parses the text on the fly, which makes it slower for frequent access but useful when you need to preserve the original input.
• jsonb: Stores JSON in a decomposed binary format. It does not preserve whitespace, key order, or duplicate keys (last one wins). This format is faster to query and supports indexing (like GIN indexes), making it the better choice for most use cases where you'll actually use the data.

Analogy: a scanned document vs. a structured spreadsheet

Imagine you receive a customer form. The json type is like keeping a scanned PDF: faithful to the original but slow to search or update a single field. The jsonb type is like transcribing that form into a spreadsheet: you lose the font and layout, but you can instantly sort, filter, and update individual fields. For most real-world workloads, you want the spreadsheet — jsonb.

The core trade-off

Pro tip: When in doubt, choose jsonb. The json type is primarily useful when you need to keep the exact original text — such as an API response for auditing — but you almost never need that in a typical application.

How it works step by step

Storing JSON in PostgreSQL follows a simple flow: create a table with a jsonb column, insert data, and then query using the dedicated operators and functions.

Step 1: Create a table with a JSON column

You can use jsonb like any other data type in a CREATE TABLE statement. Here’s an example of a table for storing user profiles with a preferences column:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    preferences JSONB
);

Step 2: Insert JSON data

You can insert JSON using a string literal with explicit cast or as a parameterized value from your application. PostgreSQL will validate that it’s valid JSON and store it in binary format.

INSERT INTO users (name, preferences)
VALUES ('Alice', '{"theme": "dark", "notifications": {"email": true, "sms": false}}');

If the JSON is invalid, PostgreSQL will raise an error and reject the insert — that’s your first line of validation.

Step 3: Query JSON fields

Use the arrow operators to extract values. The -> operator returns JSON (preserving type), while ->> returns text. For nested access, chain operators or use the #> path operators.

-- Get the theme (returns JSON)
SELECT preferences->'theme' FROM users WHERE name = 'Alice';

-- Get the theme as text
SELECT preferences->>'theme' FROM users WHERE name = 'Alice';

-- Get nested value: email notification (returns JSON)
SELECT preferences->'notifications'->'email' FROM users WHERE name = 'Alice';

-- Get nested value as boolean (cast required)
SELECT (preferences->'notifications'->>'email')::boolean AS email_enabled
FROM users WHERE name = 'Alice';

Step 4: Filter rows based on JSON

Use the @> containment operator to check if a JSON document contains a specified key/value pair. This is efficient when you have a GIN index.

SELECT * FROM users
WHERE preferences @> '{"theme": "dark"}';

Step 5: Update a JSON field

You can update a specific key without replacing the whole document using the jsonb_set function.

UPDATE users
SET preferences = jsonb_set(preferences, '{theme}', '"light"')
WHERE name = 'Alice';

Hands-on walkthrough

Let’s put it all together in a practical exercise. We’ll create a table to store event data from an analytics pipeline, insert records, and query them.

Setup

-- Create a table for events with JSONB payload
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_type TEXT NOT NULL,
    payload JSONB,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Insert sample events
INSERT INTO events (event_type, payload) VALUES
('page_view', '{"page": "/home", "referrer": "google"}'),
('purchase', '{"item": "laptop", "price": 1200, "currency": "USD"}'),
('signup', '{"user": {"email": "bob@example.com", "plan": "pro"}}');

Querying the data

Now let’s write a few queries to extract meaningful information.

-- List all events with a payload containing a 'price' key
SELECT id, event_type
FROM events
WHERE payload ? 'price';

-- Get the price of purchases
SELECT id, payload->>'item' AS item, (payload->>'price')::numeric AS price
FROM events
WHERE event_type = 'purchase';

-- Find all events where the user's plan is 'pro'
SELECT id, event_type
FROM events
WHERE payload @> '{"user": {"plan": "pro"}}';

Expected output:

 id | event_type 
----+------------
  1 | page_view
  2 | purchase
(2 rows)

 id |   item   | price 
----+----------+-------
  2 | laptop   |  1200
(1 row)

 id | event_type 
----+------------
  3 | signup
(1 row)

Adding an index for performance

For larger datasets, you’ll want a GIN index on jsonb columns to speed up containment queries.

CREATE INDEX idx_events_payload ON events USING GIN (payload);

This allows PostgreSQL to use the index for queries like payload @> ... and the ? key-existence operator, making them faster than a full table scan.

Compare options / when to choose what

Now that you’ve seen JSON in action, it’s time to choose where to use it. Here’s a comparison of useful approaches:

The golden rule: if you need to query the data within the JSON, use jsonb. If you only need to store and retrieve the entire document, you might be fine with plain TEXT, but you lose the ability to query specific fields efficiently. For most modern applications, jsonb is the sweet spot.

Pro tip: Use jsonb for data that has a flexible shape but is accessed frequently — like user preferences, feature flags, or integration payloads. Reserve normalized columns for fields you join on or that have strict integrity requirements.

Troubleshooting & edge cases

Storing JSON in PostgreSQL is straightforward, but there are common pitfalls that can trip you up.

1. You forgot the jsonb cast when inserting

If you insert a string literal into a jsonb column without a cast, PostgreSQL will implicitly cast it if the context is clear, but sometimes you get an error like:

ERROR: column "payload" is of type jsonb but expression is of type text

Fix: Explicitly cast the value: '{"key": "value"}'::jsonb or rely on parameterized queries from your driver, which usually handle the cast.

2. Using -> instead of ->> and getting JSON strings

When you compare a field to a string, you might hit:

SELECT * FROM users WHERE preferences->'theme' = 'dark';

This fails because -> returns JSON, and 'dark' is text. You get an error or no results. Fix: Use ->> to get text: preferences->>'theme' = 'dark'.

3. Key doesn’t exist and you get NULL

If you query a missing key, you’ll get NULL. That’s often fine, but if you try to cast that to a non-nullable type, you’ll get an error. Fix: Use COALESCE or filter with ? to check existence.

SELECT COALESCE(preferences->>'missing', 'default') FROM users;

4. Performance issues without an index

jsonb containment queries (@>) can be slow on large tables if you don’t have a GIN index. Fix: Create the index we covered above. For equality on a specific key, you can also create a functional index:

CREATE INDEX idx_users_theme ON users ((preferences->>'theme'));

5. Duplicate keys in json are silently dropped in jsonb

If you insert {"a": 1, "a": 2} into a jsonb column, only "a": 2 is kept. This can cause silent data loss if you don’t expect it. Fix: Use json if you must preserve duplicates, but remember you lose query performance.

What you learned & what's next

By now, you can explain the core idea behind storing JSON in PostgreSQL: you have two native types — json for exact text preservation and jsonb for efficient querying and indexing. You’ve completed a hands-on exercise that involved creating a table, inserting JSON, querying nested fields, updating individual keys, and adding a GIN index for performance. You know the trade-offs between normalized columns, jsonb, json, and separate document stores, and you’ve practiced troubleshooting common pitfalls like type mismatches, missing keys, and index optimization.

You’re now ready to move to the next lesson in this track, where you’ll build on these skills — perhaps by integrating JSON data with relational joins or learning advanced PostgreSQL functions for data analysis. Keep this lesson in your back pocket, because JSON in PostgreSQL is a tool you’ll reach for again and again in real-world schemas.