如何修改新的 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>')
其中
应该是字符串,
可以是 to_jsonb()
接受的任何类型.
Where <key>
should be string, and <value>
can be whatever type to_jsonb()
accepts.
对于在 JSON 层次结构深处设置值,可以使用 jsonb_set()
函数:
For setting a value deep in a JSON hierarchy, the jsonb_set()
function can be used:
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屋!