Menu

PostgreSQL JSONB: operatory, zapytania i indeksy

Zapisywanie i odpytywanie JSON w PostgreSQL przez jsonb: operatory -> i ->>, zawieranie przez @>, jsonb_set i inne aktualizacje, zamiana tablic na wiersze, indeksy GIN oraz json vs jsonb.

Na tej stronie są działające edytory: edytuj, uruchamiaj i od razu zobacz wynik.

jsonb to binarny typ kolumny JSON w PostgreSQL. Pola odczytujesz przez -> (zwraca jsonb) i ->> (zwraca text), wiersze znajdujesz operatorem zawierania @>, a pola zmieniasz przez jsonb_set albo ||. Indeks GIN sprawia, że te zapytania są szybkie.

Pasują zamówienia 1 i 3. data->'customer' zwraca zagnieżdżony obiekt jako jsonb, ->>'name' wyciąga imię jako tekst, a ->> z rzutowaniem zamienia sumę na liczbę.

Odczyt wartości z JSONB

OperatorZwracaPrzykład
-> klucz albo indeksjsonbdata->'customer', data->'tags'->0
->> klucz albo indekstextdata->>'status'
#> ścieżkajsonbdata#>'{customer,city}'
#>> ścieżkatextdata#>>'{customer,city}'
[...] indeksowanie (PostgreSQL 14+)jsonbdata['customer']['city']

Indeksy tablic zaczynają się od 0, a ujemny indeks liczy od końca (-> -1 to ostatni element). Brakujący klucz daje SQL NULL, a nie błąd, więc zamówienie 3 powyżej ma miasto NULL.

Używaj ->> do porównań z tekstem albo do rzutowania: data->>'status' = 'paid'. Używaj ->, żeby nawigować dalej. Częsty błąd to data->'status' = 'paid'. PostgreSQL czyta 'paid' jako JSON, słowo bez cudzysłowu nie jest poprawnym JSON, a zapytanie kończy się błędem "invalid input syntax for type json".

Wyszukiwanie wierszy po zawartości JSON

@> sprawdza, czy lewa wartość zawiera prawą. Działa na każdej głębokości i wewnątrz tablic, a obsługuje go indeks GIN:

Produkty 1 i 2 mają rozmiar M i cenę; tylko 2 kosztuje więcej niż 30. Operatory wyszukiwania:

  • @> zawiera, <@ jest zawarty w.
  • ? klucz (albo napis w tablicy) istnieje, ?| istnieje którykolwiek z kluczy, ?& istnieją wszystkie.
  • @? i @@ wykonują zapytanie JSON path (PostgreSQL 12+), do porównań wewnątrz dokumentu.

Aktualizacja pól w JSONB

PostgreSQL przy każdej aktualizacji przepisuje całą wartość, ale te funkcje pozwalają opisać tylko zmianę:

  • jsonb_set(target, path, new_value) podmienia jedną wartość pod ścieżką.
  • - usuwa klucz, a #- usuwa zagnieżdżoną ścieżkę, na przykład '{notify,sms}'.
  • || dodaje albo nadpisuje klucze najwyższego poziomu.

Od PostgreSQL 14 możesz też przypisywać przez indeksowanie: SET prefs['notify']['email'] = 'false'.

Dwie pułapki:

  • jsonb_set zwraca NULL, gdy nowa wartość to SQL NULL, a UPDATE czyści wtedy całą kolumnę. Przekaż COALESCE(to_jsonb(x), 'null') albo użyj jsonb_set_lax (PostgreSQL 13+), które domyślnie zapisuje JSON null.
  • jsonb_set dodaje brakujący ostatni klucz, ale nie brakujących rodziców: ustawienie '{a,b}' na {} zostawia je bez zmian. Przypisanie przez indeksowanie je tworzy.

Zamiana tablic JSON na wiersze

jsonb_array_elements zwraca jeden wiersz na element tablicy, a jsonb_to_recordset zamienia tablicę obiektów na typowane kolumny. Od PostgreSQL 17 standardowe JSON_TABLE robi jedno i drugie w jednym miejscu:

A-1 sprzedał się 3 razy, a B-2 raz. W drugą stronę jsonb_build_object('id', id, 'name', name) buduje obiekt z kolumn, a jsonb_agg zbiera wiersze w tablicę (zobacz string_agg i array_agg).

Indeksowanie JSONB przez GIN

Indeks GIN na całej kolumnie obsługuje @>, ?, ?|, ?&, @? i @@:

CREATE INDEX orders_data_idx ON orders USING gin (data);

-- smaller and faster, but only for @>, @? and @@
CREATE INDEX orders_data_path_idx ON orders USING gin (data jsonb_path_ops);

-- one field compared with = or a range: a B-tree on the expression
CREATE INDEX orders_status_idx ON orders ((data->>'status'));

Pisz zapytanie w formie, którą obsługuje indeks. WHERE data @> '{"status": "paid"}' może użyć indeksu GIN. WHERE data->>'status' = 'paid' nie może; potrzebuje indeksu na wyrażeniu. Test zakresu, na przykład (data->>'total')::numeric > 50, wymaga indeksu na dokładnie tym wyrażeniu.

JSON vs JSONB

jsonjsonb
Przechowywany jakoTekst wejściowySparsowana postać binarna
Białe znaki, kolejność kluczy, powtórzone kluczeZachowaneUsunięte; wygrywa ostatni powtórzony
ZapisTrochę szybszy (tylko walidacja)Parsowany przy wstawianiu
Odczyt i zapytaniaParsowany przy każdym dostępieSzybki
@>, ? i indeksy GINNieTak

json zwraca tekst bez zmian; jsonb zostawia tylko "a": 3 i zmienia kolejność kluczy. Używaj jsonb, chyba że musisz zwrócić oryginalny tekst bajt w bajt, na przykład zapisany payload webhooka. Pola, które ma każdy wiersz albo które potrzebują klucza obcego, trzymaj w zwykłych kolumnach. Oficjalna dokumentacja funkcji JSON wymienia każdy operator i funkcję.

Najczęściej zadawane pytania

Czym różni się -> od ->> w PostgreSQL?

-> zwraca wartość jako jsonb, więc możesz dalej nawigować (data->'customer'->'city'). ->> zwraca ją jako text, czyli to, co porównujesz z napisem albo rzutujesz na liczbę: (data->>'total')::numeric.

Jak sprawdzić, czy tablica JSONB zawiera wartość?

Użyj zawierania: data->'tags' @> '["gift"]' albo data @> '{"tags": ["gift"]}', żeby sprawdzać od góry dokumentu. Dla tablicy napisów działa też data->'tags' ? 'gift'. Indeks GIN na data przyspiesza drugą formę.

Czy JSONB zachowuje kolejność kluczy?

Nie. jsonb przechowuje klucze we własnej kolejności, usuwa białe znaki i z powtórzonych kluczy zostawia tylko ostatni. Jeśli potrzebujesz dokładnie oryginalnego tekstu, użyj typu json.

Jak sprawdzić, czy klucz istnieje w JSONB?

data ? 'key' jest prawdą, gdy obiekt najwyższego poziomu ma ten klucz, nawet jeśli jego wartość to JSON null. ?| przyjmuje tablicę i jest prawdą, jeśli istnieje którykolwiek klucz, a ?&, jeśli istnieją wszystkie.

Czy pole w JSONB może mieć klucz obcy albo NOT NULL?

Nie bezpośrednio. Ograniczenie CHECK może sprawdzić pole (CHECK (data ? 'email')), ale klucze obce wymagają prawdziwej kolumny. Pola, które ma każdy wiersz i do których odwołują się inne tabele, powinny być zwykłymi kolumnami.

Ilustracja języków programowania w Coddy

Ucz się programowania z Coddy

Zacznij