Postgres 9.4의 JSONB 유형 열에 대한 업데이트 작업 수행 방법

Postgres 9.4의 JSONB 유형 열에 대한 업데이트 작업 수행 방법

Postgres 9.4 데이터 유형 JSONB에 대한 문서를 살펴보면, JSONB 열에 대한 업데이트를 수행하는 방법이 즉시 명확하지 않습니다.

JSONB 유형 및 기능에 대한 설명서:

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

예를 들어, 저는 다음과 같은 기본 테이블 구조를 가지고 있습니다.

CREATE TABLE test(id serial, data jsonb);

삽입은 다음과 같이 쉽습니다.

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

이제 '데이터' 열을 어떻게 업데이트해야 합니까?잘못된 구문입니다.

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

이 문서가 제가 놓친 명백한 장소에 보관되어 있습니까?감사해요.

9할 수 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열이 최종 버전으로 대체되었습니다.

관계형 데이터베이스 내에서 조작하려는 정형화된 일반 데이터에는 JSON 문서를 사용하지 않는 것이 좋습니다.정규화된 관계형 설계를 대신 사용합니다.

JSON은 주로 RDBMS 내에서 조작할 필요가 없는 전체 문서를 저장하기 위한 것입니다. 관련:

• 인덱스가 있는 JSONB 대 hstore

Postgres에서 행을 업데이트하면 항상 전체 행의 새 버전이 작성됩니다.그것이 Postgres의 MVCC 모델의 기본 원리입니다.성능 측면에서 볼 때 JSON 개체 내의 단일 데이터를 변경하든 모두 변경하든 거의 중요하지 않습니다. 행의 새 버전을 작성해야 합니다.

따라서 설명서의 권장 사항은 다음과 같습니다.

JSON 데이터는 테이블에 저장할 때 다른 데이터 유형과 동일한 동시성 제어 고려사항을 따릅니다.큰 문서를 저장할 수는 있지만 모든 업데이트는 전체 행에 대한 행 수준 잠금을 획득합니다.업데이트 트랜잭션 간의 잠금 경합을 줄이기 위해 JSON 문서를 관리 가능한 크기로 제한하는 것이 좋습니다.이상적으로는 JSON 문서는 각각 비즈니스 규칙에 따라 독립적으로 수정할 수 있는 더 작은 데이텀으로 합리적으로 세분화할 수 없는 원자 데이텀을 나타내야 합니다.

요점은 JSON 객체 내부의 모든 것을 수정하려면 수정된 객체를 열에 할당해야 한다는 것입니다.포스트그레스는 제작 및 조작에 제한적인 수단을 제공합니다.json데이터를 저장할 수 있습니다.9.2 버전 이후로 새로운 버전이 출시될 때마다 툴이 상당히 증가했습니다.하지만 주요 내용은 다음과 같습니다.항상 완전한 수정 개체를 열에 할당해야 하며 Postgres는 업데이트에 대해 항상 새 행 버전을 작성합니다.

Postgres 9.3 이상의 도구로 작업하는 몇 가지 기술:

• 새 Postgre 내의 필드를 수정하려면 어떻게 합니까?SQL JSON 데이터 유형?

이 답변은 SO에 대한 다른 모든 답변과 마찬가지로 많은 반대표를 끌어 모았습니다.사람들은 이 아이디어를 좋아하지 않는 것 같습니다. 정규화된 설계가 일반 데이터에 더 적합합니다.Craig Ringer의 이 훌륭한 블로그 게시물은 다음과 같이 더 자세히 설명합니다.

• "포스트그리SQL 안티패턴:불필요한 json/hstore 동적 열"

크레이그와 저와 같은 Postgres의 또 다른 공식 기고자인 Laurenz Albe의 블로그 게시물:

• 포스트그레의 JSONSQL: 올바른 사용 방법

이것은 9.4에서 작동하는 기존 확장 jsonbx를 기반으로 Andrew Dunstan이 설정한 jsonb_set 형식으로 9.5에서 제공됩니다.

이 문제가 발생하여 매우 빠른 해결책(9.4.5 이전 버전에서 고착됨)을 원하는 경우 다음과 같은 잠재적인 해결책이 있습니다.

테스트 테이블 생성

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

jsonb 값을 변경하도록 문 업데이트

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

최종적으로 승인된 답변은 jsonb 개체의 개별 부분을 수정할 수 없다는 점에서 정확합니다(9.4.5 이전 버전). 그러나 jsonb 열을 문자열(::TEXT)로 캐스팅한 다음 문자열을 조작하여 jsonb 양식(:jsonb)으로 다시 캐스트할 수 있습니다.

두 가지 중요한 주의 사항이 있습니다.

1. 이것은 json에서 "my-name"과 동일한 모든 값을 바꿉니다(같은 값을 가진 여러 개체가 있는 경우).
2. 이는 9.5를 사용하는 경우 jsonb_set만큼 효율적이지 않습니다.

'name' 특성 업데이트:

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

예를 들어 'name' 및 'interval' 특성을 제거하려면 다음을 수행합니다.

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

이 질문은 postgres 9.4의 맥락에서 질문되었지만, postgres 9.5에서 JSONB 필드에 대한 하위 문서 작성/업데이트/삭제 작업은 확장 기능 없이 데이터베이스에서 기본적으로 지원된다는 것을 알아야 합니다.

참조: JSONB 연산자 및 함수 수정

저는 Postgres 9.4에서 재귀적으로 작동하는 작은 함수를 스스로 작성했습니다.저도 같은 문제가 있었습니다(Postgres 9.5에서 이 두통을 일부 해결한 것은 잘한 일입니다.어쨌든 기능은 다음과 같습니다(당신에게 잘 작동하기를 바랍니다).

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)

보다시피 심층적으로 분석하고 필요한 경우 값을 업데이트/추가합니다.

가능성: UPDATE test SET data = 'my-other-name':::json WHERE id = 1;

데이터가 json 유형인 제 경우에도 작동했습니다.

Matheus de Oliveira는 postgresql에서 JSON CRUD 작업을 위한 편리한 기능을 만들었습니다.\i 디렉티브를 사용하여 가져올 수 있습니다.데이터 유형이 jsonb인 경우 함수의 jsonb 포크를 확인합니다.

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

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

전체 열을 업데이트하는 것이 제게 도움이 되었습니다.

UPDATE test SET data='{"name": "my-other-name", "tags": ["tag1", "tag2"]}' where id=1;

언급URL : https://stackoverflow.com/questions/26703476/how-to-perform-update-operations-on-columns-of-type-jsonb-in-postgres-9-4