[解決済み] PostgreSQL。JSONカラムから属性を削除する

質問

json型のカラムからいくつかの属性を削除したい。

テーブルの

CREATE TABLE my_table( id VARCHAR(80), data json);
INSERT INTO my_table (id, data) VALUES (
  'A', 
  '{"attrA":1,"attrB":true,"attrC":["a", "b", "c"]}'
);

さて、ここで attrB をカラムから data .

次のようなものです。 alter table my_table drop column data->'attrB'; のようなものがあればいいと思います。でも、テンポラリテーブルを使った方法でも十分です。

どのように解決するのですか？

更新 : 9.5+では、明示的に jsonb (もし、あなたが json 型のカラムがある場合、キャストを使用して修正を適用することができます)。

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() .

関連項目: PostgreSQL内での他のJSON操作。

• 新しい PostgreSQL JSON データタイプ内のフィールドを変更するにはどうすればよいですか?

オリジナルの回答 (PostgreSQL 9.3にも適用されます)。

少なくともPostgreSQL 9.3を持っていれば、オブジェクトをペアに分割して json_each() でペアに分割し、不要なフィールドをフィルタリングして、再度jsonを手動で構築することができます。こんな感じ。

SELECT data::text::json AS before,
       ('{' || array_to_string(array_agg(to_json(l.key) || ':' || l.value), ',') || '}')::json AS after
FROM (VALUES ('{"attrA":1,"attrB":true,"attrC":["a","b","c"]}'::json)) AS v(data),
LATERAL (SELECT * FROM json_each(data) WHERE "key" <> 'attrB') AS l
GROUP BY data::text

9.2 (またはそれ以下) では不可能です。

編集 :

より便利なのは、関数を使って json フィールドの属性をいくつでも削除できる関数を作ることです。

編集2 : string_agg() よりも安価である。 array_to_string(array_agg())

CREATE OR REPLACE FUNCTION "json_object_delete_keys"("json" json, VARIADIC "keys_to_delete" TEXT[])
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT COALESCE(
  (SELECT ('{' || string_agg(to_json("key") || ':' || "value", ',') || '}')
   FROM json_each("json")
   WHERE "key" <> ALL ("keys_to_delete")),
  '{}'
)::json
$function$;

この機能を使えば、あとは以下のクエリを実行するだけです。

UPDATE my_table
SET data = json_object_delete_keys(data, 'attrB');