使用postgresql 9.3我可以选择JSON数据类型的特定字段，但如何使用UPDATE修改它们?我在postgresql文档或网上任何地方都找不到这样的例子。我尝试了一些显而易见的方法:
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...
即使下面的函数不能满足这个请求(函数json_object_agg在PostgreSQL 9.3中不可用)，下面的函数对于任何为PostgreSQL 9.4寻找||运算符的人来说都是有用的，因为在即将到来的PostgreSQL 9.5中实现了:
CREATE OR REPLACE FUNCTION jsonb_merge(left JSONB, right JSONB)
RETURNS JSONB
AS $$
SELECT
CASE WHEN jsonb_typeof($1) = 'object' AND jsonb_typeof($2) = 'object' THEN
(SELECT json_object_agg(COALESCE(o.key, n.key), CASE WHEN n.key IS NOT NULL THEN n.value ELSE o.value END)::jsonb
FROM jsonb_each($1) o
FULL JOIN jsonb_each($2) n ON (n.key = o.key))
ELSE
(CASE WHEN jsonb_typeof($1) = 'array' THEN LEFT($1::text, -1) ELSE '['||$1::text END ||', '||
CASE WHEN jsonb_typeof($2) = 'array' THEN RIGHT($2::text, -1) ELSE $2::text||']' END)::jsonb
END
$$ LANGUAGE sql IMMUTABLE STRICT;
GRANT EXECUTE ON FUNCTION jsonb_merge(jsonb, jsonb) TO public;
CREATE OPERATOR || ( LEFTARG = jsonb, RIGHTARG = jsonb, PROCEDURE = jsonb_merge );
