[json] 새로운 PostgreSQL JSON 데이터 유형 내에서 필드를 어떻게 수정합니까?

Program / 글쓴이

postgresql 9.3에서 JSON 데이터 유형의 특정 필드를 선택할 수 있지만 UPDATE를 사용하여 어떻게 수정합니까? postgresql 설명서 또는 온라인 어디에서나 이에 대한 예를 찾을 수 없습니다. 나는 명백한 것을 시도했다 :

postgres=# create table test (data json);
CREATE TABLE
postgres=# insert into test (data) values ('{"a":1,"b":2}');
INSERT 0 1
postgres=# select data->'a' from test where data->>'b' = '2';
 ?column?
----------
 1
(1 row)
postgres=# update test set data->'a' = to_json(5) where data->>'b' = '2';
ERROR:  syntax error at or near "->"
LINE 1: update test set data->'a' = to_json(5) where data->>'b' = '2...

답변

업데이트 : PostgreSQL 9.5 에는 jsonbPostgreSQL 자체에 일부 조작 기능이 있습니다 (그러나 값 json을 조작하는 데 캐스트는 필요 하지 않습니다 json).

2 개 이상의 JSON 객체 병합 (또는 연결 배열) :

SELECT jsonb '{"a":1}' || jsonb '{"b":2}', -- will yield jsonb '{"a":1,"b":2}'
       jsonb '["a",1]' || jsonb '["b",2]'  -- will yield jsonb '["a",1,"b",2]'

따라서 간단한 키 설정 은 다음을 사용하여 수행 할 수 있습니다.

SELECT jsonb '{"a":1}' || jsonb_build_object('<key>', '<value>')

어디에서 <key>문자열이되어야하는지, <value>어떤 유형이든지 to_jsonb()받아 들일 수 있습니다 .

들어 JSON 계층 구조에서 값 깊은 설정 의 jsonb_set()기능을 사용할 수 있습니다 :

SELECT jsonb_set('{"a":[null,{"b":[]}]}', '{a,1,b,0}', jsonb '{"c":3}')
-- will yield jsonb '{"a":[null,{"b":[{"c":3}]}]}'

의 전체 매개 변수 목록 jsonb_set():

jsonb_set(target         jsonb,
          path           text[],
          new_value      jsonb,
          create_missing boolean default true)

pathJSON 배열 색인을 포함 할 수 있으며 JSON 배열의 끝부터 계산되는 음의 정수가 포함될 수 있습니다. 그러나 존재하지 않지만 양의 JSON 배열 인덱스는 요소를 배열 끝에 추가합니다.

SELECT jsonb_set('{"a":[null,{"b":[1,2]}]}', '{a,1,b,1000}', jsonb '3', true)
-- will yield jsonb '{"a":[null,{"b":[1,2,3]}]}'

옵션 (원래 값을 모두 유지하면서) JSON 배열로 삽입 의 jsonb_insert()기능을 사용할 수있다 ( 9.6+에서 이러한 기능 만이 섹션 ) :

SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b,0}', jsonb '2')
-- will yield jsonb '{"a":[null,{"b":[2,1]}]}', and
SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b,0}', jsonb '2', true)
-- will yield jsonb '{"a":[null,{"b":[1,2]}]}'

의 전체 매개 변수 목록 jsonb_insert():

jsonb_insert(target       jsonb,
             path         text[],
             new_value    jsonb,
             insert_after boolean default false)

다시 말하지만 pathJSON 배열의 끝부터 계산되는 음의 정수입니다 .

그래서 f.ex. JSON 배열의 끝에 추가는 다음과 같이 수행 할 수 있습니다.

SELECT jsonb_insert('{"a":[null,{"b":[1,2]}]}', '{a,1,b,-1}', jsonb '3', true)
-- will yield jsonb '{"a":[null,{"b":[1,2,3]}]}', and

그러나,이 기능은 약간 다르게 (이상 작동 jsonb_set())이 경우 path에는 targetJSON 개체의 핵심입니다. 이 경우 키를 사용하지 않을 때 JSON 객체에 대한 새 키-값 쌍만 추가합니다. 사용하면 오류가 발생합니다.

SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,c}', jsonb '[2]')
-- will yield jsonb '{"a":[null,{"b":[1],"c":[2]}]}', but
SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b}', jsonb '[2]')
-- will raise SQLSTATE 22023 (invalid_parameter_value): cannot replace existing key

JSON 객체 또는 배열에서 키 또는 인덱스를 삭제하는 것은 -연산자를 사용하여 수행 할 수 있습니다 .

SELECT jsonb '{"a":1,"b":2}' - 'a', -- will yield jsonb '{"b":2}'
       jsonb '["a",1,"b",2]' - 1    -- will yield jsonb '["a","b",2]'

JSON 계층에서 깊이 삭제 는 #-연산자를 사용하여 수행 할 수 있습니다 .

SELECT '{"a":[null,{"b":[3.14]}]}' #- '{a,1,b,0}'
-- will yield jsonb '{"a":[null,{"b":[]}]}'

9.4 의 경우 원래 답변의 수정 된 버전 (아래)을 사용할 수 있지만 JSON 문자열을 집계하는 대신을 사용하여 json 객체로 직접 집계 할 수 있습니다 json_object_agg().

원래 답변 : 순수한 SQL에서도 가능합니다 (plpython 또는 plv8 제외) (9.3 이상 필요, 9.2에서는 작동하지 않음)

CREATE OR REPLACE FUNCTION "json_object_set_key"(
  "json"          json,
  "key_to_set"    TEXT,
  "value_to_set"  anyelement
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')::json
  FROM (SELECT *
          FROM json_each("json")
         WHERE "key" <> "key_to_set"
         UNION ALL
        SELECT "key_to_set", to_json("value_to_set")) AS "fields"
$function$;

SQLFiddle

편집 :

여러 키와 값을 설정하는 버전 :

CREATE OR REPLACE FUNCTION "json_object_set_keys"(
  "json"          json,
  "keys_to_set"   TEXT[],
  "values_to_set" anyarray
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')::json
  FROM (SELECT *
          FROM json_each("json")
         WHERE "key" <> ALL ("keys_to_set")
         UNION ALL
        SELECT DISTINCT ON ("keys_to_set"["index"])
               "keys_to_set"["index"],
               CASE
                 WHEN "values_to_set"["index"] IS NULL THEN 'null'::json
                 ELSE to_json("values_to_set"["index"])
               END
          FROM generate_subscripts("keys_to_set", 1) AS "keys"("index")
          JOIN generate_subscripts("values_to_set", 1) AS "values"("index")
         USING ("index")) AS "fields"
$function$;

편집 2 : @ErwinBrandstetter가 언급했듯이 위의 기능은 소위처럼 작동 UPSERT합니다 (존재하는 경우 필드 업데이트, 존재하지 않는 경우 삽입). 변형은 다음과 같습니다 UPDATE.

CREATE OR REPLACE FUNCTION "json_object_update_key"(
  "json"          json,
  "key_to_set"    TEXT,
  "value_to_set"  anyelement
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT CASE
  WHEN ("json" -> "key_to_set") IS NULL THEN "json"
  ELSE (SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')
          FROM (SELECT *
                  FROM json_each("json")
                 WHERE "key" <> "key_to_set"
                 UNION ALL
                SELECT "key_to_set", to_json("value_to_set")) AS "fields")::json
END
$function$;

편집 3 : 여기 UPSERT에는 키 경로 (키는 내부 객체 만 참조 할 수 있으며 내부 배열은 지원되지 않음)에 위치한 리프 값을 설정 하고이 답변의 첫 번째 함수를 사용하는 재귀 변형 입니다.

CREATE OR REPLACE FUNCTION "json_object_set_path"(
  "json"          json,
  "key_path"      TEXT[],
  "value_to_set"  anyelement
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT CASE COALESCE(array_length("key_path", 1), 0)
         WHEN 0 THEN to_json("value_to_set")
         WHEN 1 THEN "json_object_set_key"("json", "key_path"[l], "value_to_set")
         ELSE "json_object_set_key"(
           "json",
           "key_path"[l],
           "json_object_set_path"(
             COALESCE(NULLIF(("json" -> "key_path"[l])::text, 'null'), '{}')::json,
             "key_path"[l+1:u],
             "value_to_set"
           )
         )
       END
  FROM array_lower("key_path", 1) l,
       array_upper("key_path", 1) u
$function$;

업데이트 : 기능이 압축되었습니다.

답변

9.5에서는 jsonb_set-

UPDATE objects
SET body = jsonb_set(body, '{name}', '"Mary"', true)
WHERE id = 1;

여기서 body는 jsonb 열 유형입니다.

답변

Postgresql 9.5에서는 다음을 수행 할 수 있습니다.

UPDATE test
SET data = data - 'a' || '{"a":5}'
WHERE data->>'b' = '2';

또는

UPDATE test
SET data = jsonb_set(data, '{a}', '5'::jsonb);

누군가 jsonb 값의 많은 필드를 한 번에 업데이트하는 방법을 물었습니다. 테이블을 생성한다고 가정 해 봅시다.

CREATE TABLE testjsonb ( id SERIAL PRIMARY KEY, object JSONB );

그런 다음 실험 행을 삽입하십시오.

INSERT INTO testjsonb
VALUES (DEFAULT, '{"a":"one", "b":"two", "c":{"c1":"see1","c2":"see2","c3":"see3"}}');

그런 다음 행을 업데이트합니다.

UPDATE testjsonb SET object = object - 'b' || '{"a":1,"d":4}';

다음은 무엇입니까?

  1. 필드를 업데이트합니다
  2. b 필드를 제거합니다
  3. d 필드 추가

데이터 선택 :

SELECT jsonb_pretty(object) FROM testjsonb;

결과는 다음과 같습니다.

      jsonb_pretty
-------------------------
 {                      +
     "a": 1,            +
     "c": {             +
         "c1": "see1",  +
         "c2": "see2",  +
         "c3": "see3",  +
     },                 +
     "d": 4             +
 }
(1 row)

내부 필드를 업데이트하려면 concat 연산자를 사용하지 마십시오 ||. 대신 jsonb_set을 사용하십시오. 간단하지 않습니다 :

UPDATE testjsonb SET object =
jsonb_set(jsonb_set(object, '{c,c1}','"seeme"'),'{c,c2}','"seehim"');

{c, c1}에 대해 concat 연산자 사용 :

UPDATE testjsonb SET object = object || '{"c":{"c1":"seedoctor"}}';

{c, c2} 및 {c, c3}을 제거합니다.

더 많은 전력을 얻으 려면 postgresql json 함수 문서 에서 전력을 찾으십시오 . #-운영자, jsonb_set기능 및 기능에 관심이있을 수 있습니다 jsonb_insert.

답변

@pozs의 답변을 바탕으로 다음은 일부 유용한 PostgreSQL 함수입니다. (PostgreSQL 9.3 이상 필요)

키로 삭제 : 키로 JSON 구조에서 값을 삭제합니다.

CREATE OR REPLACE FUNCTION "json_object_del_key"(
  "json"          json,
  "key_to_del"    TEXT
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT CASE
  WHEN ("json" -> "key_to_del") IS NULL THEN "json"
  ELSE (SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')
          FROM (SELECT *
                  FROM json_each("json")
                 WHERE "key" <> "key_to_del"
               ) AS "fields")::json
END
$function$;

키별 재귀 삭제 : 키 경로로 JSON 구조에서 값을 삭제합니다. (@pozs json_object_set_key기능 필요 )

CREATE OR REPLACE FUNCTION "json_object_del_path"(
  "json"          json,
  "key_path"      TEXT[]
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT CASE
  WHEN ("json" -> "key_path"[l] ) IS NULL THEN "json"
  ELSE
     CASE COALESCE(array_length("key_path", 1), 0)
         WHEN 0 THEN "json"
         WHEN 1 THEN "json_object_del_key"("json", "key_path"[l])
         ELSE "json_object_set_key"(
           "json",
           "key_path"[l],
           "json_object_del_path"(
             COALESCE(NULLIF(("json" -> "key_path"[l])::text, 'null'), '{}')::json,
             "key_path"[l+1:u]
           )
         )
       END
    END
  FROM array_lower("key_path", 1) l,
       array_upper("key_path", 1) u
$function$;

사용 예 :

s1=# SELECT json_object_del_key ('{"hello":[7,3,1],"foo":{"mofu":"fuwa", "moe":"kyun"}}',
                                 'foo'),
            json_object_del_path('{"hello":[7,3,1],"foo":{"mofu":"fuwa", "moe":"kyun"}}',
                                 '{"foo","moe"}');

 json_object_del_key |          json_object_del_path
---------------------+-----------------------------------------
 {"hello":[7,3,1]}   | {"hello":[7,3,1],"foo":{"mofu":"fuwa"}}

답변

UPDATE test
SET data = data::jsonb - 'a' || '{"a":5}'::jsonb
WHERE data->>'b' = '2'

이것은 PostgreSQL 9.5에서 작동하는 것 같습니다

답변

필드 유형이 json 인 경우 다음이 적합합니다.

UPDATE
table_name
SET field_name = field_name::jsonb - 'key' || '{"key":new_val}'
WHERE field_name->>'key' = 'old_value'.

연산자 ‘-‘는 왼쪽 피연산자에서 키 / 값 쌍 또는 문자열 요소를 삭제합니다. 키 / 값 쌍은 해당 키 값을 기준으로 일치합니다.

연산자 ‘||’ 두 개의 jsonb 값을 새로운 jsonb 값으로 연결합니다.

이들은 jsonb 연산자이므로 다음과 같이 typecast해야합니다. : jsonb

추가 정보 : JSON 함수 및 연산자

여기 내 메모를 읽을 수 있습니다

답변

PostgreSQL 9.4에서는 다음과 같은 파이썬 함수를 구현했습니다. PostgreSQL 9.3에서도 작동 할 수 있습니다.

create language plpython2u;

create or replace function json_set(jdata jsonb, jpaths jsonb, jvalue jsonb) returns jsonb as $$
import json

a = json.loads(jdata)
b = json.loads(jpaths)

if a.__class__.__name__ != 'dict' and a.__class__.__name__ != 'list':
  raise plpy.Error("The json data must be an object or a string.")

if b.__class__.__name__ != 'list':
   raise plpy.Error("The json path must be an array of paths to traverse.")

c = a
for i in range(0, len(b)):
  p = b[i]
  plpy.notice('p == ' + str(p))

  if i == len(b) - 1:
    c[p] = json.loads(jvalue)

  else:
    if p.__class__.__name__ == 'unicode':
      plpy.notice("Traversing '" + p + "'")
      if c.__class__.__name__ != 'dict':
        raise plpy.Error("  The value here is not a dictionary.")
      else:
        c = c[p]

    if p.__class__.__name__ == 'int':
      plpy.notice("Traversing " + str(p))
      if c.__class__.__name__ != 'list':
        raise plpy.Error("  The value here is not a list.")
      else:
        c = c[p]

    if c is None:
      break

return json.dumps(a)
$$ language plpython2u ;

사용법 예 :

create table jsonb_table (jsonb_column jsonb);
insert into jsonb_table values
('{"cars":["Jaguar", {"type":"Unknown","partsList":[12, 34, 56]}, "Atom"]}');

select jsonb_column->'cars'->1->'partsList'->2, jsonb_column from jsonb_table;

update jsonb_table
set jsonb_column = json_set(jsonb_column, '["cars",1,"partsList",2]', '99');

select jsonb_column->'cars'->1->'partsList'->2, jsonb_column from jsonb_table;

이전 고용주, I는 (안으로서 텍스트로 JSON 데이터를 조작하기위한 C 함수 세트를 쓴 참고 json하거나 jsonb, 예를 들어 PostgreSQL의 7, 8 및 9에 대해 타입)으로 데이터를 추출 json_path('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']')하여 데이터를 설정 json_path_set('{"obj":[12, 34, {"num":-45.67}]}', '$.obj[2]['num']', '99.87')등등. 작업에 약 3 일이 걸렸으므로 레거시 시스템에서 실행하고 여유 시간이 필요한 경우에는 노력할 가치가 있습니다. C 버전이 파이썬 버전보다 훨씬 빠르다고 생각합니다.