如何修改新 PostgreSQL JSON 数据类型中的字段? [英] How do I modify fields inside the new PostgreSQL JSON datatype?
问题描述
使用 postgresql 9.3 我可以 SELECT JSON 数据类型的特定字段，但是如何使用 UPDATE 修改它们?我在 postgresql 文档或在线任何地方都找不到这方面的任何示例.我已经尝试了明显的:
With postgresql 9.3 I can SELECT specific fields of a JSON data type, but how do you modify them using UPDATE? I can't find any examples of this in the postgresql documentation, or anywhere online. I have tried the obvious:
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 中，PostgreSQL 本身有一些 jsonb 操作功能(但对于 json 没有；强制类型转换需要操作 json 值).
Update: With PostgreSQL 9.5, there are some jsonb manipulation functionality within PostgreSQL itself (but none for json; casts are required to manipulate json values).
合并 2 个(或更多)JSON 对象(或连接数组):
Merging 2 (or more) JSON objects (or concatenating arrays):
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() 接受的任何类型.
Where <key> should be string, and <value> can be whatever type to_jsonb() accepts.
在 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)
path 也可以包含 JSON 数组索引 &出现在那里的负整数从 JSON 数组的末尾开始计数.但是，一个不存在但为正的 JSON 数组索引会将元素附加到数组的末尾:
path can contain JSON array indexes too & negative integers that appear there count from the end of JSON arrays. However, a non-existing, but positive JSON array index will append the element to the end of the array:
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+ 中；此函数仅在本节中):
For inserting into JSON array (while preserving all of the original values), the jsonb_insert() function can be used (in 9.6+; this function only, in this section):
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)
同样，path 中出现的负整数从 JSON 数组的末尾开始计数.
Again, negative integers that appear in path count from the end of JSON arrays.
所以，前任.可以通过以下方式追加到 JSON 数组的末尾:
So, f.ex. appending to an end of a JSON array can be done with:
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
但是，当 target 中的 path 是 JSON 对象的键时，此函数的工作方式略有不同(与 jsonb_set()).在这种情况下，它只会在不使用该键时为 JSON 对象添加一个新的键值对.如果使用了会报错:
However, this function is working slightly differently (than jsonb_set()) when the path in target is a JSON object's key. In that case, it will only add a new key-value pair for the JSON object when the key is not used. If it's used, it will raise an error:
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 对象(或数组)中删除键(或索引) 可以使用 - 运算符来完成:
Deleting a key (or an index) from a JSON object (or, from an array) can be done with the - operator:
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 层次结构的深处删除:
Deleting, from deep in a JSON hierarchy can be done with the #- operator:
SELECT '{"a":[null,{"b":[3.14]}]}' #- '{a,1,b,0}'
-- will yield jsonb '{"a":[null,{"b":[]}]}'
对于 9.4，您可以使用原始答案的修改版本(如下)，但是您可以直接使用 json_object_agg() 聚合成一个 json 对象，而不是聚合一个 JSON 字符串.
For 9.4, you can use a modified version of the original answer (below), but instead of aggregating a JSON string, you can aggregate into a json object directly with json_object_agg().
原答案:在纯 SQL 中也可以(没有 plpython 或 plv8)(但需要 9.3+，不适用于 9.2)
Original answer: It is possible (without plpython or plv8) in pure SQL too (but needs 9.3+, will not work with 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$;
编辑:
一个版本，它设置多个键 &价值观:
A version, which sets multiple keys & values:
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:
Edit 2: as @ErwinBrandstetter noted these functions above works like a so-called UPSERT (updates a field if it exists, inserts if it does not exist). Here is a variant, which only 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) 一个叶值(并使用此答案中的第一个函数)，位于一个键-路径(其中键只能引用内部对象，不支持内部数组):
Edit 3: Here is recursive variant, which can set (UPSERT) a leaf value (and uses the first function from this answer), located at a key-path (where keys can only refer to inner objects, inner arrays not supported):
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$;
更新:添加了用另一个给定键替换现有 json 字段键的功能.可以方便地在迁移或数据结构修改等其他场景中更新数据类型.
Updated: Added function for replacing an existing json field's key by another given key. Can be in handy for updating data types in migrations or other scenarios like data structure amending.
CREATE OR REPLACE FUNCTION json_object_replace_key(
json_value json,
existing_key text,
desired_key text)
RETURNS json AS
$BODY$
SELECT COALESCE(
(
SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}')
FROM (
SELECT *
FROM json_each(json_value)
WHERE key <> existing_key
UNION ALL
SELECT desired_key, json_value -> existing_key
) AS "fields"
-- WHERE value IS NOT NULL (Actually not required as the string_agg with value's being null will "discard" that entry)
),
'{}'
)::json
$BODY$
LANGUAGE sql IMMUTABLE STRICT
COST 100;
更新:函数现在被压缩了.
这篇关于如何修改新 PostgreSQL JSON 数据类型中的字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
