Query JSON Fields in PostgreSQL

You’ve mastered relational tables, but the moment your application logs a JSON blob — a webhook payload, user preferences, or a product catalog with dynamic attributes — you realize that plain SQL WHERE clauses don’t reach inside. Without PostgreSQL’s JSON querying powers, you’d either parse the JSON in your application code (slow, memory-hungry) or force the data into rigid columns (losing the flexibility that brought you to JSON in the first place). This lesson shows you how to query JSON fields directly in SQL, treating jsonb as a first-class citizen: filter, extract, and aggregate nested data with concise, index-friendly expressions. By the end, you’ll stop fearing the blob columns and start harnessing them.

The problem this lesson solves

You have a products table with a details column of type jsonb. Each row holds a document like this:

{"name": "Ergonomic Keyboard", "price": 89.99, "tags": ["electronics", "office"]}

Now the boss asks: “How many products have the tag ‘office’ and cost less than $100?” With a plain text column, you’d write spaghetti code to parse every row in Python, filter, and count. That approach doesn’t scale — every request must pull the entire table into memory, and there’s no chance of using a database index.

PostgreSQL’s jsonb type, combined with its query operators and functions, lets you push that logic deep into SQL. You can filter, extract, aggregate, and even index nested values — everything you need to answer the boss without leaving the database.

This lesson focuses on the query side: how to query JSON fields using the ->, ->>, @>, and ? operators, plus functions like jsonb_extract_path and jsonb_each. You’ll see how to handle nested objects, arrays, and missing keys, and when to reach for a GIN index.

Core concept / mental model

Think of a jsonb column as a document in miniature. Relational tables are rigid spreadsheets; JSON columns are flexible filing cabinets. To query inside that cabinet, PostgreSQL gives you two families of tools:

1. Path operators — -> and ->> let you navigate the JSON tree (like json.loads() in Python, but inside SQL). -> returns JSON (still typed as jsonb), while ->> returns text (or the JSON primitive as a scalar string).
2. Containment operators — @> checks whether a JSON document contains a given subdocument (like in but for whole structures), and ? checks whether a key, array element, or string exists.

A quick analogy: Imagine a nested Python dictionary {'user': {'age': 30, 'name': 'Ada'}}. The -> operator is like accessing data['user'] (the result is still a dict), and ->> is like converting to a string when you finally get to a scalar. Containment @> is like data == {...} but for a subset — does the document contain this exact nested fragment?

Key types you’ll meet:

For querying, always prefer jsonb — it supports GIN indexes and efficient operators. The rest of this lesson uses jsonb.

How it works step by step

To query JSON fields, you follow a repeatable pattern: create the table, insert data, build a query, then optimize with an index. Here’s the logical sequence:

1. Define the column as jsonb. Use jsonb not json when you plan to filter or index.
2. Insert your documents. You can cast strings or use jsonb_build_object for programmatic construction.
3. Use the right operator. For filtering on a scalar, use ->> to get text and compare. For filtering on a nested object or array, use @> with a JSON fragment.
4. Extract values for SELECT using ->>> or jsonb_extract_path_text for readability.
5. Add a GIN index when the table grows large, especially for @> and ? operators.

Why the operator choice matters:

• -> returns jsonb. If you compare it to a string, you need an explicit cast: WHERE details -> 'price' = '89.99'::jsonb.
• ->> returns text. You can compare directly to a string literal: WHERE details ->> 'price' = '89.99'.
• @> is the heavyweight — it checks deep equality of a whole subdocument. It’s perfect for “does this document contain this exact nested structure?” For example, details @> '{"tags": ["office"]}' checks if the tags array includes "office".

The cause-and-effect is straightforward: the operator you choose determines the data type of the result, which determines how you compare it in the WHERE clause. Getting this wrong causes the classic “operator does not exist” errors you’ll fix in the troubleshooting section.

Hands-on walkthrough

Let’s build a realistic example — a products table with JSON details, then run several queries. Fire up psql and follow along.

Step 1: Create and populate

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,        -- redundant? keep for demo
    details JSONB NOT NULL
);

INSERT INTO products (name, details) VALUES
('Ergonomic Keyboard', '{"category": "electronics", "price": 89.99, "in_stock": true, "tags": ["office", "peripheral"]}'),
('Standing Desk', '{"category": "furniture", "price": 399.00, "in_stock": false, "tags": ["office"]}'),
('Monitor 24"', '{"category": "electronics", "price": 149.99, "in_stock": true, "tags": ["remote_work"]}');

Step 2: Extract scalar fields with ->>

To get a product’s price as a number (for sorting or calculating), cast the text result:

SELECT name, (details ->> 'price')::numeric AS price
FROM products
WHERE (details ->> 'category') = 'electronics'
ORDER BY price;

Output:

        name        | price
--------------------+-------
 Ergonomic Keyboard | 89.99
 Monitor 24"        | 149.99
(2 rows)

Notice ->> returns text; we cast to numeric for arithmetic and sorting.

Step 3: Nested extraction with #>> and jsonb_extract_path

For a nested path like settings.theme.color, you can chain operators or use a path function. First, let’s add a nested document:

UPDATE products
SET details = details || '{"settings": {"theme": {"color": "dark"}}}'::jsonb
WHERE id = 1;

Now extract the color:

SELECT name,
       details #>> '{settings,theme,color}' AS color_with_path_op,
       jsonb_extract_path_text(details, 'settings', 'theme', 'color') AS color_with_func
FROM products
WHERE id = 1;

Output:

        name        | color_with_path_op | color_with_func
--------------------+--------------------+-----------------
 Ergonomic Keyboard | dark                | dark
(1 row)

Both produce the same result. #>> uses a text array, jsonb_extract_path_text uses variadic arguments — pick whichever reads better.

Step 4: Filter with containment @> and existence ?

Find products tagged “office”:

SELECT name, details
FROM products
WHERE details @> '{"tags": ["office"]}';

Output:

        name        |                                  details
--------------------+----------------------------------------------------------------------------------------------------
 Ergonomic Keyboard | {"category": "electronics", "price": 89.99, ... "tags": ["office", "peripheral"]}
 Standing Desk      | {"category": "furniture", "price": 399.0, ... "tags": ["office"]}
(2 rows)

The @> operator checks if the document contains that exact fragment — note it finds the array element regardless of order.

Check if a key exists at the top level:

SELECT name
FROM products
WHERE details ? 'in_stock';

This returns all rows because every product has in_stock. You can also use ?| for “any of these keys” and ?& for “all keys”:

SELECT name FROM products WHERE details ?| array['in_stock', 'warranty'];
SELECT name FROM products WHERE details ?& array['category', 'price'];

Step 5: Aggregate JSON content with jsonb_each

Suppose you want to count how many products have each category. Use jsonb_each_text to expand the JSON into key-value rows:

SELECT key, count(*)
FROM products, jsonb_each_text(details) AS kv(key, value)
WHERE key = 'category'
GROUP BY key, value;

Output:

   key    | count
----------+-------
 category |     3
(1 row)

In practice, you’d likely query details ->> 'category' directly, but jsonb_each shines when you don’t know the JSON structure ahead of time — for example, user-defined metadata.

Step 6 (optional): Optimize with a GIN index

For fast @> and ? queries, create a GIN index on the JSONB column:

CREATE INDEX idx_products_details ON products USING GIN (details);

Now run the @> query again; you can use EXPLAIN to confirm the index is used. For queries that filter on a specific path, a jsonb_path_ops index is more compact and often faster:

CREATE INDEX idx_products_details_ops ON products USING GIN (details jsonb_path_ops);

Pro tip: Only index if you actually query that path frequently. JSONB columns are already compact, but a GIN index adds write overhead.

Compare options / when to choose what

Here’s a decision map for common query patterns:

Alternatives:

• Use jsonb_path_ops indexes for path-specific queries — smaller and faster for @> but only supports @> and ?, not all operators.
• For complex traversal, consider SQL/JSON Path expressions (jsonb_path_exists, jsonb_path_query) — part of the SQL standard, more powerful but steeper learning curve.

Troubleshooting & edge cases

“operator does not exist: jsonb = text”

You wrote WHERE details ->> 'price' = 89.99 and got an error because ->> returns text. The right side is a numeric literal — PostgreSQL can’t compare text to integer. Cast the left side: (details ->> 'price')::numeric = 89.99.

Missing keys return NULL, not error

Consider SELECT details ->> 'color' FROM products WHERE id=1. If color isn’t in the document, you get NULL, not an exception. This is fine, but be careful in WHERE clauses: WHERE details -> 'color' IS NULL won’t match a row where the key doesn’t exist — it will match a row where the key exists with value null. Use ? to check existence first.

Counting array elements with jsonb_array_length

If you want catalog items with exactly two tags:

SELECT name FROM products WHERE jsonb_array_length(details -> 'tags') = 2;

This fails if tags isn’t an array or is missing. Use NULLIF or check existence first.

GIN index not being used

If you created the index but EXPLAIN shows a seq scan, your query might use ->> which isn’t directly supported by the default GIN index. For scalar equality on a path, create an expression index:

CREATE INDEX idx_products_price ON products ((details ->> 'price')::numeric);

Then filter with WHERE (details ->> 'price')::numeric = 89.99.

What you learned & what's next

You’ve learned to query JSON fields in PostgreSQL: use ->> for scalar extraction, @> for containment filtering, ? for key existence, and jsonb_each for unnesting. You can now handle nested structures, avoid the pitfalls of text vs JSONB types, and create indexes to keep queries fast. You also know when to prefer jsonb over json and when to reach for expression indexes.

This knowledge plugs directly into your next stop in the track — likely indexing strategies or full-text search on top of JSON data. With JSONB querying in your belt, you’re ready to design schemas that blend relational rigor with document flexibility, and to optimize them with the right indexes. Keep practicing — query a real webshop’s product table and measure the impact of a GIN index with EXPLAIN ANALYZE.