Daxili verilənlərin çıxarılması: jsonb_to_recordset()

İndi isə JSONB formatında verilənlərlə işləməyin bir az daha çətin ssenarilərinə keçirik — nested verilənlərin çıxarılması və onları cədvəl sətirlərinə çevirmək. Deyəcəksən, bu nəyə lazımdır? Çox sadə! Təsəvvür elə, sənə alış-verişlərin massivindən ibarət JSON obyekti veriblər və səndən bütün alışların ümumi məbləğini hesablamağı və ya onları hesabat üçün cədvəl şəklində çıxarmağı istəyiblər. Bunu indi birgə həll edəcəyik!

Niyə JSON ilə sadəcə mətn və ya struktur kimi işləmək olmur? Gəlin bir ssenariyə baxaq. Bir çox real tətbiqlərdə verilənlər JSON massivləri şəklində saxlanılır:

[
  { "id": 1, "product_name": "Laptop", "price": 1200 },
  { "id": 2, "product_name": "Smartphone", "price": 800 },
  { "id": 3, "product_name": "Tablet", "price": 400 }
]

Bu rahatdır, amma analiz zamanı tez-tez massivləri cədvələ çevirmək lazımdır – filtrasiya, sortlama və aqreqasiya kimi əməliyyatlar üçün. Təsəvvür elə: «Bütün sifarişlər 500 dollardan çox olanlar». JSONB özü-özlüyündə bunu istədiyimiz qədər rahat etmir. Məhz burada jsonb_to_recordset() köməyə gəlir.

jsonb_to_recordset() ilə işləmək

jsonb_to_recordset() funksiyası JSONB obyektlərinin massivini cədvəl sətirlərinə çevirir. O, hər massiv elementini sətirə, açarları isə sütunlara çevirir. Bu funksiya nested və ya massiv obyektlər olan verilənlərlə işləyərkən əvəzolunmazdır.

Sintaksis

SELECT *
FROM jsonb_to_recordset('[ JSONB massiv ]') AS alias(kolonka1 TİP, kolonka2 TİP, ...);

• [ JSONB massiv ]: çıxaracağımız verilənlərin olduğu JSON obyektləri massivi.
• AS alias: nəticə cədvəlinə müvəqqəti ad veririk.
• kolonka1 TİP, kolonka2 TİP: sütunların adlarını və onların tipini təyin edirik (məsələn, INTEGER, TEXT, NUMERIC).

Nümunə: JSONB massivini sətirlərə çevirmək

Tutaq ki, belə bir cədvəlimiz var:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT,
    products JSONB
);

Və cədvəldə belə verilənlər var:

İndi tapşırıq: bütün sifarişlər üçün bütün məhsulların siyahısını cədvəl şəklində çıxarmaq. Bunu jsonb_to_recordset() ilə belə edirik:

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC);

Nəticə:

Nümunə: verilənlərin filtrasiya olunması

Gəlin tapşırığı bir az çətinləşdirək. Yalnız 100 dollardan baha olan məhsulları göstərmək istəyirik:

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
WHERE
    p.price > 100;

Nəticə:

Nümunə: verilənlərin aqreqasiyası

Bəs bütün sifarişlərdəki məhsulların ümumi məbləğini hesablamaq necə? Sadəcə aqreqat funksiyalardan istifadə edirik:

SELECT
    o.customer_name,
    SUM(p.price) AS total_amount
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
GROUP BY
    o.customer_name;

Nəticə:

Vacib qeydlər

JSON massivinin strukturu bütün obyektlər üçün eyni olmalıdır. Əgər obyektin açarları fərqlidirsə və ya nested strukturlar varsa, səhvlər və ya gözlənilməz nəticələr ala bilərsən.

Çıxarılan sütunlar üçün düzgün verilən tipini təyin et. Məsələn, açar tarixdirsə DATE, rəqəmlər üçün NUMERIC və ya INTEGER istifadə et.

Unutma ki, jsonb_to_recordset() yalnız JSONB massivləri üçün işləyir; tək obyektlər üçün işləmir.

Tipik səhvlər və onların qarşısını alma yolları

Verilən tipinin səhv istifadəsi: Əgər JSONB massivində fərqli tiplər varsa (məsələn, rəqəm yerinə string), bu səhvə səbəb olacaq. Funksiyanı işlətməzdən əvvəl verilənləri düzgün formata salmaq məsləhətdir.

Yanlış açarlara müraciət: Əgər massivdəki obyektlərin birində açar yoxdursa, səhv çıxacaq. Sorğunu işlətməzdən əvvəl verilənlərin strukturunu yoxla.

Verilən yoxdur: Əgər JSONB sütunu boşdursa (NULL), funksiya heç bir nəticə qaytarmayacaq. Belə hallarda COALESCE() kimi yoxlamalar əlavə et.

Praktik tətbiq

jsonb_to_recordset() real işlərdə geniş istifadə olunur: sifarişlərin işlənməsi, hesabatların analizi, istifadəçi fəaliyyətinin loglanması və xarici API-lərin işlənməsi kimi. Məsələn:

• Online mağazalarda məhsul massivlərini rahatlıqla cədvələ çevirib hesabatlar qurmaq olur.
• REST API JSON formatında verilənlər qaytarırsa, onları PostgreSQL ilə analiz etmək rahatdır.
• Analitika tətbiqləri tez-tez bu funksiyanı çoxsəviyyəli verilənləri işləmək üçün istifadə edir.