Examples of Advanced Queries with JSONB

In the previous lectures, we covered the basics of JSONB: how to create, update, and fetch data. Now it's time for the real deal—advanced queries that show off the full power of this data type.

Imagine an online store with a product catalog. Every product has basic info (name, ID), but the features can be totally different: a laptop has RAM and CPU, clothes have sizes and materials, books have authors and genres. Storing all that in separate tables? Not fun. In JSONB? Perfect! But how do you find all products of a certain brand, sort them by price, or get stats by category? How do you work with data that's not in regular columns, but hidden deep inside a JSON structure?

Today we'll break down real scenarios: from simple filtering to complex queries with grouping and aggregation. You'll see how JSONB turns PostgreSQL into a flexible tool for working with any kind of data.

Filtering Data in JSONB

Filtering data is like using a tea strainer: you keep only what you need and get rid of the rest. With JSONB, things get even cooler, since you can filter not just by regular columns, but also by data buried deep inside the JSON structure.

Operators for Filtering JSONB:

• @> — "JSONB-contains". Checks if the JSONB object contains the specified subset.
• ? — "Key exists". Checks if the specified key is present in the JSONB object.
• ?| — "Any key exists". Checks if at least one of the specified keys is present.
• ?& — "All keys exist". Checks if all the specified keys are present.

Example: Filtering by key and its value. Let's say we have a products table with a details column that stores JSONB info about products:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    details JSONB
);

Sample data:

INSERT INTO products (name, details) VALUES
('Laptop', '{"brand": "Dell", "price": 1200, "specs": {"ram": "16GB", "cpu": "i7"}}'),
('Smartphone', '{"brand": "Apple", "price": 800, "specs": {"ram": "4GB", "cpu": "A15"}}'),
('Tablet', '{"brand": "Samsung", "price": 500, "specs": {"ram": "8GB", "cpu": "Exynos"}}');

Result:

To find all products with brand "Apple":

SELECT *
FROM products 
WHERE details @> '{"brand": "Apple"}';

Result:

If you want to find all products that have the specs key, use the ? operator:

SELECT *
FROM products 
WHERE details ? 'specs';

Result:

All rows have the details field and the specs key.

Sorting Data in JSONB

Sometimes you need to sort data not by regular columns, but by values inside JSONB. For that, you can use the ->> operator (extracts value as text) and CAST to convert the text value to the type you need.

Example: let's sort products by price:

SELECT *
FROM products 
ORDER BY (details->>'price')::INTEGER;

Result:

Grouping Data in JSONB

Grouping lets you aggregate data and show stats. For example, you can find out how many products each brand has.

Example: let's count the number of products for each brand:

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
GROUP BY details->>'brand';

Result:

Practical Examples

Filtering and grouping. Let's count how many products over 600 there are for each brand:

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
WHERE (details->>'price')::INTEGER > 600
GROUP BY details->>'brand';

Result:

Sorting after grouping. Now let's sort brands by product count:

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
GROUP BY details->>'brand'
ORDER BY product_count DESC;

Complex Query: Filtering, Sorting, Grouping

Let's say you want to find brands that have products over 600 and pick the cheapest product for each brand. Here's how you do it:

WITH filtered_products AS (
    SELECT *
    FROM products
    WHERE (details->>'price')::INTEGER > 600
)
SELECT
    details->>'brand' AS brand,
    MIN((details->>'price')::INTEGER) AS min_price
FROM filtered_products
GROUP BY details->>'brand'
ORDER BY min_price;

Result:

Common Mistakes and Tips

Mistake: Using operators wrong. Don't mix up -> and ->>: the first returns an object, the second returns a text value.

Mistake: Performance issues. If you run complex queries a lot, create a GIN index on your JSONB column.

Mistake: Type problems. Values from JSONB are strings, so don't forget to use CAST.

Example of creating an index:

CREATE INDEX idx_products_details ON products USING GIN (details);

Now filtering like details @> '{"brand": "Apple"}' will work way faster.