Jak wykonać operacje aktualizacji na kolumnach typu JSONB w Postgres 9.4

Przeglądając dokumentację JSONB typu danych Postgres 9.4, nie od razu wiem, jak wykonać aktualizacje kolumn JSONB.

Dokumentacja dotycząca typów i funkcji JSONB:

http://www.postgresql.org/docs/9.4/static/functions-json.html http://www.postgresql.org/docs/9.4/static/datatype-json.html

Jako przykłady mam taką podstawową strukturę tabeli:

CREATE TABLE test(id serial, data jsonb);

Wstawianie jest łatwe, jak w:

INSERT INTO test(data) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}');

Jak zaktualizować kolumnę „dane”? To jest nieprawidłowa składnia:

UPDATE test SET data->'name' = 'my-other-name' WHERE id = 1;

Czy jest to udokumentowane gdzieś, że przegapiłem? Dzięki.

W idealnym przypadku nie używasz dokumentów JSON dla uporządkowanych, zwykłych danych, którymi chcesz manipulować w relacyjnej bazie danych. Zamiast tego użyj znormalizowanego projektu relacyjnego .

JSON jest przede wszystkim przeznaczony do przechowywania całych dokumentów, które nie wymagają manipulacji w RDBMS. Związane z:

• JSONB z indeksowaniem vs. hstore

Aktualizacja wiersza w Postgres zawsze powoduje zapisanie nowej wersji całego wiersza. To podstawowa zasada modelu MVCC Postgresa . Z punktu widzenia wydajności nie ma znaczenia, czy zmienisz pojedynczy fragment danych w obiekcie JSON, czy wszystkie: należy napisać nową wersję wiersza.

Dlatego rady w instrukcji :

Dane JSON podlegają tym samym zagadnieniom dotyczącym kontroli współbieżności, co każdy inny typ danych przechowywanych w tabeli. Chociaż przechowywanie dużych dokumentów jest możliwe, należy pamiętać, że każda aktualizacja powoduje zablokowanie całego wiersza na poziomie wiersza. Rozważ ograniczenie dokumentów JSON do rozmiaru umożliwiającego zarządzanie, aby zmniejszyć rywalizację o blokady między aktualizacjami transakcji. Idealnie byłoby, gdyby każdy dokument JSON reprezentował niepodzielne dane, które narzucają reguły biznesowe, nie mogą być dalej dzielone na mniejsze dane, które można modyfikować niezależnie.

Istota tego: aby zmodyfikować cokolwiek wewnątrz obiektu JSON, musisz przypisać zmodyfikowany obiekt do kolumny. Postgres zapewnia ograniczone możliwości tworzenia i manipulowania jsondanymi oprócz swoich możliwości przechowywania. Arsenał narzędzi znacznie się powiększył z każdą nową wersją od wersji 9.2. Ale zasada pozostaje: zawsze musisz przypisać całkowicie zmodyfikowany obiekt do kolumny, a Postgres zawsze zapisuje nową wersję wiersza dla każdej aktualizacji.

Niektóre techniki pracy z narzędziami Postgres 9.3 lub nowszym:

• Jak modyfikować pola w nowym typie danych PostgreSQL JSON?

Ta odpowiedź przyciągnęła mniej więcej tyle samo głosów przeciw, co wszystkie moje inne odpowiedzi razem na SO . Ludziom nie podoba się ten pomysł: znormalizowany projekt jest lepszy w przypadku danych niedynamicznych. Ten doskonały wpis na blogu Craiga Ringera wyjaśnia bardziej szczegółowo:

• „Anty-wzorce PostgreSQL: niepotrzebne kolumny dynamiczne json / hstore”

Jeśli możesz zaktualizować do Postgresql 9.5, jsonb_setpolecenie jest dostępne, jak wspominali inni.

W każdej z poniższych instrukcji SQL pominąłem whereklauzulę dotyczącą zwięzłości; oczywiście chciałbyś to dodać.

Nazwa aktualizacji:

UPDATE test SET data = jsonb_set(data, '{name}', '"my-other-name"');

Zastąp tagi (w przeciwieństwie do dodawania lub usuwania tagów):

UPDATE test SET data = jsonb_set(data, '{tags}', '["tag3", "tag4"]');

Zastąpienie drugiego tagu (z indeksem 0):

UPDATE test SET data = jsonb_set(data, '{tags,1}', '"tag5"');

Dołącz tag ( będzie działać, o ile będzie mniej niż 999 tagów; zmiana argumentu 999 na 1000 lub więcej generuje błąd . Wydaje się, że nie ma to już miejsca w Postgres 9.5.3; można użyć znacznie większego indeksu) :

UPDATE test SET data = jsonb_set(data, '{tags,999999999}', '"tag6"', true);

Usuń ostatni tag:

UPDATE test SET data = data #- '{tags,-1}'

Złożona aktualizacja (usuń ostatni tag, wstaw nowy tag i zmień nazwę):

UPDATE test SET data = jsonb_set(
    jsonb_set(data #- '{tags,-1}', '{tags,999999999}', '"tag3"', true), 
    '{name}', '"my-other-name"');

Należy zauważyć, że w każdym z tych przykładów w rzeczywistości nie aktualizujesz ani jednego pola danych JSON. Zamiast tego tworzysz tymczasową, zmodyfikowaną wersję danych i przypisujesz tę zmodyfikowaną wersję z powrotem do kolumny. W praktyce wynik powinien być taki sam, ale pamiętanie o tym powinno sprawić, że złożone aktualizacje, takie jak ostatni przykład, będą bardziej zrozumiałe.

W złożonym przykładzie istnieją trzy transformacje i trzy wersje tymczasowe: Po pierwsze, ostatni tag jest usuwany. Następnie ta wersja jest przekształcana przez dodanie nowego tagu. Następnie druga wersja jest przekształcana poprzez zmianę namepola. Wartość w datakolumnie zostanie zastąpiona wersją ostateczną.

To pojawi się w 9.5 w postaci jsonb_set autorstwa Andrew Dunstana w oparciu o istniejące rozszerzenie jsonbx, które działa z 9.4

Dla tych, którzy napotkali ten problem i chcą bardzo szybkiej naprawy (i utknęli w wersji 9.4.5 lub wcześniejszej), oto co zrobiłem:

Stworzenie tabeli testowej

CREATE TABLE test(id serial, data jsonb);
INSERT INTO test(data) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}');

Zaktualizuj instrukcję, aby zmienić nazwę właściwości JSONB

UPDATE test 
SET data = replace(data::TEXT,'"name":','"my-other-name":')::jsonb 
WHERE id = 1;

Ostatecznie przyjęta odpowiedź jest prawidłowa, ponieważ nie można zmodyfikować pojedynczego fragmentu obiektu jsonb (w wersji 9.4.5 lub wcześniejszej); jednak można rzutować obiekt jsonb na łańcuch (:: TEXT), a następnie manipulować łańcuchem i rzutować z powrotem na obiekt jsonb (:: jsonb).

Istnieją dwa ważne zastrzeżenia

1. spowoduje to zastąpienie wszystkich właściwości o nazwie „nazwa” w pliku json (w przypadku, gdy masz wiele właściwości o tej samej nazwie)
2. nie jest to tak wydajne jak jsonb_set, jeśli używasz 9.5

Powiedziawszy to, natknąłem się na sytuację, w której musiałem zaktualizować schemat zawartości w obiektach jsonb i był to najprostszy sposób, aby osiągnąć dokładnie to, o co prosił oryginalny plakat.

To pytanie zostało postawione w kontekście postgres 9.4, jednak nowi widzowie przychodzący na to pytanie powinni mieć świadomość, że w postgres 9.5 pod-dokument Operacje Create / Update / Delete na polach JSONB są natywnie obsługiwane przez bazę danych, bez konieczności rozbudowy Funkcje.

Zobacz: operatory i funkcje modyfikujące JSONB

zaktualizuj atrybut „name”:

UPDATE test SET data=data||'{"name":"my-other-name"}' WHERE id = 1;

a jeśli chcesz usunąć na przykład atrybuty „nazwa” i „tagi”:

UPDATE test SET data=data-'{"name","tags"}'::text[] WHERE id = 1;

Napisałem dla siebie małą funkcję, która działa rekurencyjnie w Postgres 9.4. Miałem ten sam problem (dobrze, że rozwiązali część tego bólu głowy w Postgres 9.5). W każdym razie tutaj jest funkcja (mam nadzieję, że działa dobrze):

CREATE OR REPLACE FUNCTION jsonb_update(val1 JSONB,val2 JSONB)
RETURNS JSONB AS $$
DECLARE
    result JSONB;
    v RECORD;
BEGIN
    IF jsonb_typeof(val2) = 'null'
    THEN 
        RETURN val1;
    END IF;

    result = val1;

    FOR v IN SELECT key, value FROM jsonb_each(val2) LOOP

        IF jsonb_typeof(val2->v.key) = 'object'
            THEN
                result = result || jsonb_build_object(v.key, jsonb_update(val1->v.key, val2->v.key));
            ELSE
                result = result || jsonb_build_object(v.key, v.value);
        END IF;
    END LOOP;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

Oto przykładowe użycie:

select jsonb_update('{"a":{"b":{"c":{"d":5,"dd":6},"cc":1}},"aaa":5}'::jsonb, '{"a":{"b":{"c":{"d":15}}},"aa":9}'::jsonb);
                            jsonb_update                             
---------------------------------------------------------------------
 {"a": {"b": {"c": {"d": 15, "dd": 6}, "cc": 1}}, "aa": 9, "aaa": 5}
(1 row)

Jak widać, analizuje głęboko i aktualizuje / dodaje wartości w razie potrzeby.

Może: UPDATE test SET data = '"moja-inna-nazwa"' :: json WHERE id = 1;

Zadziałało w moim przypadku, gdzie dane są typu json

Matheus de Oliveira stworzył przydatne funkcje dla operacji JSON CRUD w postgresql. Można je importować za pomocą dyrektywy \ i. Zwróć uwagę na widelec jsonb funkcji, jeśli jsonb, jeśli typ danych.

9.3 json https://gist.github.com/matheusoliveira/9488951

9.4 jsonb https://gist.github.com/inindev/2219dff96851928c2282