[postgresql] Postgres 9.4에서 JSONB 유형의 열에 대해 업데이트 작업을 수행하는 방법

Postgresql 9.5로 업그레이드 할 수 있다면 jsonb_set다른 사람들이 언급했듯이 명령을 사용할 수 있습니다.

다음의 각 SQL 문에서 where간결성을 위해 절을 생략했습니다. 분명히 다시 추가하고 싶을 것입니다.

업데이트 이름 :

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

태그를 추가하거나 제거하는 것이 아니라 태그를 교체하십시오.

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

두 번째 태그 교체 (0 색인) :

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

태그를 추가합니다 ( 999 개 미만 의 태그 가있는 한 작동합니다. 인수를 999에서 1000 이상으로 변경하면 오류가 발생합니다 . Postgres 9.5.3에서는 더 이상 발생 하지 않으며 더 큰 인덱스를 사용할 수 있음) :

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

마지막 태그를 제거하십시오.

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

복잡한 업데이트 (마지막 태그 삭제, 새 태그 삽입 및 이름 변경) :

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

이러한 각 예에서 실제로 JSON 데이터의 단일 필드를 업데이트하지는 않습니다. 대신 임시 수정 버전의 데이터를 만들고 수정 된 버전을 다시 열에 할당합니다. 실제로 결과는 동일해야하지만이를 염두에두면 마지막 예와 같이 복잡한 업데이트가 더 이해하기 쉬워집니다.

복잡한 예에는 세 가지 변형과 세 가지 임시 버전이 있습니다. 먼저 마지막 태그가 제거됩니다. 그런 다음 새 태그를 추가하여 해당 버전이 변환됩니다. 다음으로, 두 번째 버전은 name필드 를 변경하여 변환됩니다 . data열의 값 이 최종 버전으로 바뀝니다.

답변

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

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

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

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

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;

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)