如何修改新的PostgreSQL JSON数据类型内的字段? [英] How do I modify fields inside the new PostgreSQL JSON datatype?
问题描述
使用postgresql 9.3，我可以选择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...
推荐答案
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.
因此，f.ex.附加到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$;
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$;
更新:现在压缩函数.
这篇关于如何修改新的PostgreSQL JSON数据类型内的字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
