Przykłady zaawansowanych zapytań z JSONB

W poprzednich lekcjach ogarnęliśmy podstawy JSONB: jak tworzyć, zmieniać i wyciągać dane. Teraz czas na prawdziwe wyzwania — złożone zapytania, które pokażą całą moc tego typu danych.

Wyobraź sobie sklep internetowy z katalogiem produktów. Każdy produkt ma podstawowe info (nazwa, ID), ale cechy mogą się totalnie różnić: laptop ma RAM i CPU, ubrania — rozmiary i materiały, książki — autorów i gatunki. Trzymać to wszystko w osobnych tabelach? Słabo. W JSONB? Idealnie! Ale jak znaleźć wszystkie produkty konkretnej marki, posortować je po cenie albo policzyć statystyki po kategoriach? Jak pracować z danymi, które nie są w zwykłych kolumnach, tylko schowane głęboko w strukturze JSON?

Dzisiaj rozkminimy prawdziwe scenariusze: od prostego filtrowania po złożone zapytania z grupowaniem i agregacją. Zobaczysz, jak JSONB zamienia PostgreSQL w mega elastyczne narzędzie do pracy z dowolnymi danymi.

Filtrowanie danych w JSONB

Filtrowanie danych — to jak sitko do herbaty: zostawiasz tylko to, co trzeba, a reszta wylatuje. Z JSONB robi się jeszcze ciekawiej, bo możesz filtrować nie tylko po zwykłych kolumnach, ale też po danych schowanych głęboko w strukturze JSON.

Operatory do filtrowania JSONB:

• @> — "JSONB-zawiera". Sprawdza, czy obiekt JSONB zawiera podany podzbiór.
• ? — "Klucz obecny". Sprawdza, czy dany klucz jest w obiekcie JSONB.
• ?| — "Którykolwiek z kluczy obecny". Sprawdza, czy chociaż jeden z podanych kluczy jest obecny.
• ?& — "Wszystkie klucze obecne". Sprawdza, czy wszystkie podane klucze są obecne.

Przykład: filtrowanie po kluczu i jego wartości. Załóżmy, że mamy tabelę products z kolumną details, gdzie trzymamy info o produktach w JSONB:

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

Przykładowe dane:

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"}}');

Wynik:

Żeby znaleźć wszystkie produkty z brand "Apple":

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

Wynik:

Jeśli chcesz znaleźć wszystkie produkty, które mają klucz specs, użyj operatora ?:

SELECT *
FROM products 
WHERE details ? 'specs';

Wynik:

Wszystkie wiersze mają pole details i klucz specs.

Sortowanie danych w JSONB

Czasem musisz posortować dane nie po zwykłych kolumnach, tylko po wartościach schowanych w JSONB. Do tego używasz operatorów ->> (wyciąga wartość jako tekst) i CAST, żeby przekonwertować tekst na odpowiedni typ.

Przykład: sortujemy produkty po cenie:

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

Wynik:

Grupowanie danych w JSONB

Grupowanie pozwala agregować dane i wyciągać statystyki. Na przykład możesz sprawdzić, ile produktów przypada na każdą markę.

Przykład: policzmy liczbę produktów dla każdej marki:

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

Wynik:

Praktyczne przykłady

Filtrowanie i grupowanie. Policzmy, ile produktów droższych niż 600 ma każda marka:

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

Wynik:

Sortowanie po grupowaniu. Teraz posortujmy marki po liczbie produktów:

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

Kompleksowe zapytanie: filtrowanie, sortowanie, grupowanie

Wyobraź sobie, że chcesz znaleźć marki, które mają produkty droższe niż 600 i wybrać jeden najtańszy produkt dla każdej marki. Tak to ogarniesz:

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;

Wynik:

Typowe błędy i porady

Błąd: Złe użycie operatorów. Nie myl operatorów -> i ->>: pierwszy zwraca obiekt, drugi — wartość tekstową.

Błąd: Problemy z wydajnością. Jeśli często robisz złożone zapytania, załóż indeks GIN na kolumnie JSONB.

Błąd: Problemy z typami. Wartości z JSONB to stringi, więc pamiętaj o CAST.

Przykład tworzenia indeksu:

CREATE INDEX idx_products_details ON products USING GIN (details);

Teraz filtrowanie typu details @> '{"brand": "Apple"}' będzie działać dużo szybciej.