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

Jeśli jesteś w stanie uaktualnić do Postgresql 9.5, Komenda jsonb_set jest dostępna, jak już inni wspominali.

W każdym z poleceń follow SQL, pominąłem klauzulę where dla zwięzłości; oczywiście, chciałbyś dodać to z powrotem.

Nazwa aktualizacji:

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

Zastąp znaczniki (jako przeciwstawne do dodawania lub usuwania znaczników):

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

Zastępowanie drugiego znacznika (0-indeksowane):

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

Dodaj znacznik (to będzie działać tak długo, jak będzie mniej niż 999 znaczników; zmiana argumentu 999 na 1000 lub wyższy generuje błąd . Nie wydaje się, aby tak było 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 znacznik, wstaw nowy znacznik i zmień nazwę):

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

Ważne jest, aby pamiętać, że w każdym z tych przykładów w rzeczywistości nie aktualizujesz pojedynczego pola danych JSON. Zamiast tego tworzysz tymczasową, zmodyfikowaną wersję danych i przypisanie tej zmodyfikowanej wersji z powrotem do kolumny. W praktyce wynik powinien być taki sam, ale pamiętanie o tym powinno sprawić, że złożone aktualizacje, jak w poprzednim przykładzie, będą bardziej zrozumiałe.

W złożonym przykładzie są trzy przekształcenia i trzy wersje tymczasowe: pierwszy, ostatni znacznik jest usuwany. Następnie ta wersja jest przekształcana przez dodanie nowego tagu. Następnie druga wersja jest przekształcana przez zmianę pola name. Wartość w kolumnie data wynosi zastąpiona wersją finalną.

To nadchodzi w 9.5 w postaci jsonb_set przez Andrew Dunstan w oparciu o istniejące rozszerzenie jsonbx, które działa z 9.4

To pytanie zostało zadane w kontekście postgres 9.4, jednak nowi widzowie przychodzący na to pytanie powinni mieć świadomość, że w postgres 9.5, operacje tworzenia/aktualizowania/usuwania podkatalogu na polach JSONB są natywnie obsługiwane przez bazę danych, bez potrzeby stosowania funkcji rozszerzeń.

Patrz: operatory i funkcje modyfikujące JSONB

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

Tworzenie tabeli testów

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

Polecenie Update do zmiany nazwy właściwości jsonb

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

Ostatecznie zaakceptowana odpowiedź jest poprawna, ponieważ nie można modyfikować pojedynczego fragmentu obiektu jsonb (w wersji 9.4.5 lub wcześniejszej); można jednak rzucić obiekt jsonb na ciąg znaków (:: TEXT), a następnie manipulować ciągiem znaków i cast powrót do obiektu jsonb (:: jsonb).

Istnieją dwa ważne zastrzeżenia

1. to zastąpi wszystkie właściwości o nazwie "name" w json (w przypadku, gdy masz wiele właściwości o tej samej nazwie)
2. to nie jest tak efektywne jak jsonb_set, jeśli używasz 9.5

Z tym powiedziawszy, 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, co oryginalny plakat pytał.

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 dla Ciebie):

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.

Maybe: UPDATE test SET data = '"my-other - name"':: json WHERE id = 1;

Zadziałało z moim przypadkiem, gdzie dane są typu json

Matheus de Oliveira stworzył przydatne funkcje dla operacji JSON CRUD w postgresql. Mogą być importowane za pomocą dyrektywy \I. Zwróć uwagę na widelec funkcji jsonb if jsonb if Twój typ danych.

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

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