How to Use JSON Data Types in PostgreSQL

When I first started working with PostgreSQL, I treated it like any other relational database — rows, columns, foreign keys, the usual. It wasn’t until I ran into a project where the incoming data was wildly inconsistent (think webhook payloads from a dozen different third-party APIs) that I really appreciated what PostgreSQL’s JSON support could do for me. Instead of fighting to normalize every possible shape of data into rigid columns, I could just drop the payload into a JSON column and query it later. In this article, I want to walk you through everything I’ve learned about using JSON data types in PostgreSQL — the syntax, the practical examples, the gotchas, and the best practices I now follow on every project.

Why PostgreSQL Supports JSON at All

PostgreSQL is a relational database, but somewhere along the way it became one of the best semi-structured data stores you can use, without giving up any of the guarantees you get from a relational system. I like this because it means I don’t have to reach for a separate document store like MongoDB just because part of my data doesn’t fit neatly into columns. I can keep my transactional guarantees, my foreign keys, my JOINs, and still have a column that holds arbitrary JSON.

There are two JSON types in PostgreSQL, and understanding the difference between them is the first thing I make sure to explain to anyone new to this feature.

JSON vs JSONB: The Core Difference

PostgreSQL gives you two ways to store JSON: json and jsonb.

• json stores an exact copy of the input text. It preserves whitespace, key order, and even duplicate keys. Every time you query it, PostgreSQL has to reparse the text.
• jsonb stores the data in a decomposed binary format. It doesn’t preserve whitespace or key order, and it removes duplicate keys (keeping the last one). Because it’s already parsed into a binary structure, it’s faster to query and it supports indexing.

Honestly, in almost every project I’ve worked on, I default to jsonb. The only time I’d reach for json is if I genuinely need to preserve the original formatting or key ordering of the document — for example, if I’m archiving raw payloads for audit purposes and want byte-for-byte fidelity of the text representation. For anything I intend to query, filter, or index, jsonb wins every time.

Here’s a simple table definition that shows both:

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_type TEXT NOT NULL,
    raw_payload JSON,
    payload JSONB,
    created_at TIMESTAMPTZ DEFAULT now()
);

Inserting JSON Data

Inserting data into a JSON or JSONB column is straightforward — you just pass a JSON-formatted string, and PostgreSQL validates and stores it.

INSERT INTO events (event_type, payload)
VALUES (
    'user_signup',
    '{"user_id": 42, "email": "jane@example.com", "plan": "pro", "referrer": null}'
);

If the JSON you pass in is malformed, PostgreSQL will reject the insert with a syntax error, which is one of the nice safety nets you get compared to storing raw text in a TEXT column.

You can also build JSON objects directly in SQL using functions like jsonb_build_object and jsonb_build_array, which I find useful when I’m constructing JSON from existing relational data rather than receiving it pre-formatted from an application.

INSERT INTO events (event_type, payload)
VALUES (
    'order_placed',
    jsonb_build_object(
        'order_id', 1001,
        'items', jsonb_build_array('sku_1', 'sku_2', 'sku_3'),
        'total', 59.97
    )
);

Querying JSON Data

This is where things get interesting. PostgreSQL gives you a handful of operators to reach into a JSON document and pull out values.

The -> and ->> Operators

• -> returns the value as JSON (or JSONB).
• ->> returns the value as text.

SELECT payload -> 'user_id' AS user_id_json,
       payload ->> 'user_id' AS user_id_text
FROM events
WHERE event_type = 'user_signup';

I use ->> most of the time because I usually want to compare or cast the value, and text is easier to work with for that. But when I need to chain further into a nested object, I need -> to keep working with JSON.

Nested Access

If your JSON has nested objects, you can chain the -> operator:

SELECT payload -> 'shipping' -> 'address' ->> 'city' AS city
FROM events
WHERE event_type = 'order_placed';

The #> and #>> Path Operators

For deeper paths, it’s often cleaner to use a path array instead of chaining operators:

SELECT payload #>> '{shipping,address,city}' AS city
FROM events;

This does the exact same thing as the chained version above but reads more cleanly once you’re going three or four levels deep.

Filtering Based on JSON Values

Since ->> returns text, you can use it directly in a WHERE clause:

SELECT *
FROM events
WHERE payload ->> 'plan' = 'pro';

If you need numeric comparisons, cast the extracted text:

SELECT *
FROM events
WHERE (payload ->> 'total')::numeric > 50;

Containment and Existence Operators

One of the features I lean on constantly is the @> containment operator, which lets me check whether a JSONB document contains a given subset of key-value pairs.

SELECT *
FROM events
WHERE payload @> '{"plan": "pro"}';

This is different from ->> filtering because it can match nested structures and works efficiently with GIN indexes (more on that below).

There’s also the ? existence operator, which checks whether a key exists at the top level:

SELECT *
FROM events
WHERE payload ? 'referrer';

And its plural cousins ?| (any of these keys exist) and ?& (all of these keys exist):

SELECT * FROM events WHERE payload ?| array['referrer', 'campaign'];
SELECT * FROM events WHERE payload ?& array['user_id', 'email'];

Modifying JSONB Data

jsonb supports update operations that json doesn’t, which is another point in its favor.

Updating a Key with jsonb_set

UPDATE events
SET payload = jsonb_set(payload, '{plan}', '"enterprise"')
WHERE event_type = 'user_signup' AND payload ->> 'user_id' = '42';

The second argument is the path (as a text array), and the third is the new value, which must itself be valid JSON — notice the value "enterprise" is quoted because it’s a JSON string literal.

Adding or Removing Keys

You can merge two JSONB objects with the || operator, which is handy for adding new keys or overwriting existing ones:

UPDATE events
SET payload = payload || '{"tags": ["vip"]}'
WHERE payload ->> 'plan' = 'enterprise';

To remove a key, use the - operator:

UPDATE events
SET payload = payload - 'referrer'
WHERE payload ? 'referrer';

To remove a nested path, use #-:

UPDATE events
SET payload = payload #- '{shipping,address,city}';

Indexing JSONB Columns

Querying JSON without an index works fine on small tables, but once you’re dealing with millions of rows, sequential scans on JSONB columns become painfully slow. This is where GIN indexes come in.

CREATE INDEX idx_events_payload ON events USING GIN (payload);

A default GIN index on a JSONB column supports the @>, ?, ?|, and ?& operators efficiently. If most of your queries filter on a specific key, you can create a more targeted expression index instead:

CREATE INDEX idx_events_plan ON events ((payload ->> 'plan'));

This kind of expression index is what I reach for when I know exactly which field I’ll be filtering on most often — it’s smaller and faster than a general-purpose GIN index for that specific use case.

You can also use the jsonb_path_ops operator class, which produces a smaller, faster index specifically for containment queries (@>):

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

I usually pick jsonb_path_ops when I know I’ll almost exclusively use @> for filtering, since the index is more compact and the queries run faster. The trade-off is that jsonb_path_ops doesn’t support the ? existence operators.

Using JSONPath Queries

PostgreSQL added SQL/JSON path support a while back, which lets you run more expressive queries using the jsonpath type and the @@ and @? operators.

SELECT *
FROM events
WHERE payload @@ '$.total > 50';

Or extracting values with jsonb_path_query:

SELECT jsonb_path_query(payload, '$.items[*]') AS item
FROM events
WHERE event_type = 'order_placed';

I don’t use JSONPath every day, but when I need conditional logic or array filtering inside the JSON itself, it saves me from writing convoluted subqueries.

Working with JSON Arrays

A common use case is storing arrays inside JSONB and needing to expand them into rows. The jsonb_array_elements function is what I reach for.

SELECT id, elem
FROM events, jsonb_array_elements(payload -> 'items') AS elem
WHERE event_type = 'order_placed';

If the array contains scalar values and you want them as text rather than JSON, use jsonb_array_elements_text.

Aggregating Relational Data into JSON

Sometimes I need to go the other direction — take relational rows and produce a JSON structure, usually for an API response. jsonb_agg and jsonb_object_agg are great for this.

SELECT jsonb_agg(jsonb_build_object('id', id, 'event_type', event_type))
FROM events;

This lets me build API responses directly in SQL without an extra serialization step in application code, which has saved me a surprising amount of boilerplate over the years.

Common Use Cases I’ve Run Into

• Storing webhook and API payloads where the schema varies by provider or changes over time without a migration.
• Feature flags and configuration blobs attached to a user or tenant row, where the set of possible flags keeps growing.
• Event logging where each event type has a different shape of metadata.
• Product catalogs with variable attributes per category (a shirt has size and color; a laptop has RAM and CPU).
• Audit trails that need to capture a snapshot of a record’s state at a point in time.

Troubleshooting Tips

A few issues come up again and again when I’m working with JSON columns, so I’ll save you some time.

“Operator does not exist” errors. This almost always means you’re comparing a jsonb value to a plain text or numeric value without extracting it first. Remember: -> gives you JSON, ->> gives you text. If you’re comparing to a number, cast it: (payload ->> 'total')::numeric.

Slow queries on large JSONB columns. Check whether you actually have an index that matches your query pattern. A GIN index on the whole column won’t necessarily speed up an expression like payload ->> 'plan' = 'pro' — for that you often want a targeted expression index instead.

Unexpected key ordering or missing duplicate keys. This is expected behavior with jsonb, not a bug. If you need to preserve exact input formatting, you need json, not jsonb.

NULL handling confusion. A JSON null value stored inside a jsonb column ({"referrer": null}) is not the same as a SQL NULL. payload -> 'referrer' on that row returns a JSON null, not a SQL NULL, so IS NULL checks won’t behave the way you might expect. Use payload ->> 'referrer' IS NULL or check with the ? operator depending on what you actually mean by “missing.”

Best Practices I Follow

1. Default to jsonb unless you have a specific reason not to. The performance and indexing benefits are too good to pass up for most use cases.
2. Don’t use JSON as a substitute for proper schema design. If a field is always present, always the same type, and something you’ll query constantly, it probably deserves its own column. JSON is for the genuinely variable parts of your data.
3. Index deliberately. Don’t slap a GIN index on every JSONB column by default — figure out your actual query patterns first, then index accordingly.
4. Validate at the application layer too. PostgreSQL validates that the data is well-formed JSON, but it won’t enforce your business schema (required keys, value types) unless you add CHECK constraints or use JSON Schema validation in your application.
5. Consider CHECK constraints for critical structure. If certain keys must always exist, you can enforce that with a constraint like CHECK (payload ? 'user_id').
6. Keep documents reasonably sized. JSONB is stored using TOAST for large values, and extremely large documents can hurt performance. If you’re storing megabytes of JSON per row, reconsider your design.

A Real-World Example: Multi-Tenant Configuration

Let me walk through a scenario I’ve actually built more than once: per-tenant configuration in a SaaS product. Every tenant needs a different, evolving set of settings, and adding a new column for every possible setting would mean a migration every time product added a new toggle.

CREATE TABLE tenants (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    settings JSONB NOT NULL DEFAULT '{}'
);

INSERT INTO tenants (name, settings) VALUES
    ('Acme Corp', '{"theme": "dark", "features": {"beta_dashboard": true}, "limits": {"seats": 25}}');

Reading a nested feature flag:

SELECT name
FROM tenants
WHERE (settings -> 'features' ->> 'beta_dashboard')::boolean = true;

Updating a single nested field without touching anything else:

UPDATE tenants
SET settings = jsonb_set(settings, '{limits,seats}', '50')
WHERE id = 1;

This pattern — a JSONB “settings” or “metadata” column alongside normal relational columns for the fields that are always present — is one of the most common and genuinely useful applications of JSON support I’ve built, and it scales well because I can always add an expression index later if one particular setting becomes a hot filter path.

Casting Between JSON and Relational Rows

Sometimes I need to go from a JSON array of objects straight into a proper result set, particularly when accepting bulk data from an API. jsonb_to_recordset is the function for this:

SELECT *
FROM jsonb_to_recordset('[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]')
    AS x(id INT, name TEXT);

This is useful for bulk-inserting data that arrives as a JSON payload without writing a loop in application code:

INSERT INTO users (id, name)
SELECT * FROM jsonb_to_recordset('[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]')
    AS x(id INT, name TEXT)
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

A Note on Schema Validation

PostgreSQL doesn’t enforce a JSON Schema out of the box, but you can get partial enforcement with CHECK constraints if certain fields are non-negotiable:

ALTER TABLE tenants
ADD CONSTRAINT settings_has_theme CHECK (settings ? 'theme');

For more elaborate validation — enforcing types, required nested keys, enum-like constraints on values — I generally still validate at the application layer before the insert happens, since writing a full JSON Schema validator in SQL gets unwieldy fast. I treat the database’s JSON validation as a safety net for structural well-formedness and simple key-presence rules, not as a replacement for application-level validation of business rules.

Wrapping Up

JSON support in PostgreSQL is one of those features that quietly changes how I design schemas. I no longer feel like I have to choose between “relational” and “flexible” — I can have both in the same database, in the same transaction, with the same tooling. Start with jsonb, learn the handful of operators I covered here, add indexes once you understand your query patterns, and you’ll find that PostgreSQL handles semi-structured data about as well as any purpose-built document database, minus the operational overhead of running a second system.