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

업데이트 : 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$;

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

답변

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

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

UPDATE test
SET data = jsonb_set(data, '{a}', '5'::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}';

SELECT jsonb_pretty(object) FROM testjsonb;

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

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

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

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$;

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'

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

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;