JSONB ilə mürəkkəb sorğu nümunələri

Əvvəlki dərslərdə biz JSONB-nin əsaslarını öyrəndik: necə yaratmaq, dəyişmək və məlumat çıxarmaq olar. İndi isə əsl sınaq vaxtıdır — bu data tipinin gücünü göstərən mürəkkəb sorğulara baxacağıq.

Təsəvvür elə bir internet-mağaza var və orda məhsullar kataloqu var. Hər məhsulun əsas məlumatı var (ad, ID), amma xüsusiyyətləri tam fərqli ola bilər: noutbukda RAM və CPU var, geyimdə — ölçülər və materiallar, kitabda — müəlliflər və janrlar. Bütün bunları ayrı-ayrı cədvəllərdə saxlamaq? Narahatdır. JSONB-də? Əladır! Bəs necə edək ki, məsələn, müəyyən bir brendin bütün məhsullarını tapaq, onları qiymətə görə sıralayaq və ya kateqoriyalar üzrə statistika çıxaraq? Necə işləyək o məlumatlarla ki, onlar adi sütunlarda yox, JSON strukturunun içində gizlənib?

Bu gün biz real ssenarilərə baxacağıq: sadə filtrdən tutmuş qruplaşdırma və aqreqasiyalı kompleks sorğulara qədər. Görəcəksən ki, JSONB PostgreSQL-i istənilən məlumat üçün çevik bir alətə çevirir.

JSONB-də məlumatların filtrasiyası

Filtrasiya — elə bil çay süzgəcidir: lazım olanı saxlayırsan, qalanı atırsan. JSONB ilə isə daha maraqlıdır, çünki biz təkcə adi sütunlara görə yox, həm də JSON strukturunun dərinliyində gizlənmiş məlumatlara görə də filtrasiya edə bilirik.

JSONB üçün filtrasiya operatorları:

• @> — "JSONB-də var". Yoxlayır ki, JSONB obyektində göstərilən alt-məlumat var, ya yox.
• ? — "Açar mövcuddur". Yoxlayır ki, göstərilən açar JSONB obyektində var, ya yox.
• ?| — "Açarların hər hansı biri mövcuddur". Yoxlayır ki, göstərilən açarlardan ən azı biri var, ya yox.
• ?& — "Bütün açarlar mövcuddur". Yoxlayır ki, göstərilən bütün açarlar var, ya yox.

Nümunə: açara və onun dəyərinə görə filtrasiya. Tutaq ki, bizdə products adlı cədvəl var və details sütununda məhsullar haqqında JSONB məlumatı saxlanılır:

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

Məlumat nümunəsi:

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

Nəticə:

Bütün brand "Apple" olan məhsulları tapmaq üçün:

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

Nəticə:

Əgər sən bütün məhsulları tapmaq istəyirsənsə ki, orda specs açarı var, ? operatorundan istifadə et:

SELECT *
FROM products 
WHERE details ? 'specs';

Nəticə:

Bütün sətirlərdə details sahəsi və specs açarı var.

JSONB-də məlumatların sıralanması

Bəzən sənə lazım olur ki, məlumatları adi sütunlara görə yox, JSONB-nin içindəki dəyərlərə görə sıralayasan. Bunun üçün ->> (dəyəri mətn kimi çıxarır) və CAST operatorlarından istifadə edə bilərsən.

Nümunə: məhsulları qiymətə görə sıralayaq:

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

Nəticə:

JSONB-də məlumatların qruplaşdırılması

Qruplaşdırma sənə imkan verir ki, məlumatları aqreqasiya edəsən və statistika çıxarasan. Məsələn, hər brendə neçə məhsul düşür, onu öyrənə bilərsən.

Nümunə: hər brend üçün məhsul sayını hesablayaq:

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

Nəticə:

Praktik nümunələr

Filtrasiya və qruplaşdırma. Gəlin, hər brend üçün qiyməti 600-dən yuxarı olan məhsulların sayını hesablayaq:

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

Nəticə:

Qruplaşdırmadan sonra sıralama. İndi isə brendləri məhsul sayına görə azalan sırada sıralayaq:

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

Kompleks sorğu: filtrasiya, sıralama, qruplaşdırma

Tutaq ki, sən istəyirsən ki, qiyməti 600-dən yuxarı olan məhsulları olan brendləri tapasan və hər brend üçün ən ucuz məhsulu seçəsən. Bax belə etmək olar:

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;

Nəticə:

Tipik səhvlər və məsləhətlər

Səhv: Operatorların səhv istifadəsi. -> və ->> operatorlarını qarışdırma: birincisi obyekt qaytarır, ikincisi isə mətn dəyəri.

Səhv: Performans problemləri. Əgər tez-tez mürəkkəb sorğular edirsənsə, JSONB sütununda GIN indeksi yarat.

Səhv: Tip problemləri. JSONB-dən çıxan dəyərlər string-dir, ona görə CAST istifadə etməyi unutma.

İndeks yaratmaq nümunəsi:

CREATE INDEX idx_products_details ON products USING GIN (details);

İndi details @> '{"brand": "Apple"}' kimi filtrasiya daha sürətli işləyəcək.