Wyciąganie zagnieżdżonych danych: jsonb_to_recordset()

Lecimy dalej do bardziej zaawansowanych przypadków pracy z danymi w formacie JSONB — wyciągania zagnieżdżonych danych i zamiany ich na wiersze tabeli. Po co to w ogóle robić? Mega proste! Wyobraź sobie, że dostajesz JSON-owy obiekt z tablicą zakupów i masz policzyć sumę wszystkich zakupów albo pokazać je w tabelce do raportu. Zaraz to ogarniemy!

Czemu nie można po prostu pracować z JSON-em jak z tekstem albo strukturą? Zobaczmy na przykładzie. W wielu realnych apkach dane trzymane są jako tablice JSON:

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

To wygodne, ale jak chcesz analizować dane, często musisz zamienić tablicę na tabelę, żeby móc filtrować, sortować czy agregować. Wyobraź sobie: „Wszystkie zamówienia powyżej 500 dolarów”. Sam JSONB nie pozwala na to tak wygodnie, jakby się chciało. I tu właśnie wchodzi jsonb_to_recordset().

Praca z jsonb_to_recordset()

Funkcja jsonb_to_recordset() pozwala zamienić tablicę obiektów JSONB na wiersze tabeli. Każdy element tablicy staje się wierszem, a klucze zamieniają się w kolumny. Ta funkcja jest niezastąpiona, gdy dane są mocno zagnieżdżone albo masz tablice obiektów.

Składnia

SELECT *
FROM jsonb_to_recordset('[ tablica JSONB ]') AS alias(kolumna1 TYP, kolumna2 TYP, ...);

• [ tablica JSONB ]: tablica obiektów JSON, z której wyciągamy dane.
• AS alias: tworzymy tymczasową nazwę dla wynikowej tabeli.
• kolumna1 TYP, kolumna2 TYP: określamy, jak mają się nazywać kolumny i jakie typy danych będą miały (np. INTEGER, TEXT, NUMERIC).

Przykład: zamiana tablicy JSONB na wiersze

Załóżmy, że mamy taką tabelę:

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

I w tabeli są takie dane:

Teraz zadanie: wyświetlić listę wszystkich produktów ze wszystkich zamówień w formie tabeli. Tak to robimy z jsonb_to_recordset():

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);

Wynik:

Przykład: filtrowanie danych

Podkręcamy poziom. Chcemy pokazać tylko te produkty z zamówień, które kosztują więcej niż 100 dolarów:

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;

Wynik:

Przykład: agregacja danych

A co powiesz na policzenie sumy wszystkich produktów w zamówieniach? Po prostu używamy funkcji agregujących:

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;

Wynik:

Ważne uwagi

Upewnij się, że struktura tablicy JSON jest taka sama dla wszystkich obiektów. Jeśli jakiś obiekt ma inne klucze albo zagnieżdżone struktury, możesz dostać błąd albo dziwne wyniki.

Dobrze ustaw typy danych dla wyciąganych kolumn. Na przykład, jeśli klucz to data, użyj DATE, dla liczb — NUMERIC albo INTEGER.

Pamiętaj, że jsonb_to_recordset() działa tylko na tablicach JSONB; z pojedynczymi obiektami nie zadziała.

Typowe błędy i jak ich unikać

Zły typ danych: jeśli w tablicy JSONB są wartości z różnymi typami (np. string zamiast liczby), będzie błąd. Najlepiej przerobić dane na właściwy format przed użyciem funkcji.

Zły klucz: jeśli klucz nie istnieje w którymś obiekcie tablicy, będzie błąd. Sprawdź strukturę danych przed zapytaniem.

Brak danych: jeśli kolumna JSONB jest pusta (NULL), funkcja nie zwróci wyników. W takich przypadkach dodaj sprawdzenie, np. COALESCE().

Praktyczne zastosowanie

jsonb_to_recordset() jest szeroko używane w realnych zadaniach, np. do obsługi zamówień, analizy raportów, logowania akcji użytkowników czy przetwarzania zewnętrznych API. Przykłady:

• W sklepach internetowych łatwo zamienisz tablice produktów na tabele i zrobisz raporty.
• REST API może zwracać dane w formacie JSON, które wygodnie analizować w PostgreSQL.
• Apki analityczne często używają tej funkcji do ogarniania skomplikowanych, wielopoziomowych danych.