使用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...
我为自己写了一个小函数，在Postgres 9.4中递归地工作。下面是函数(我希望它对你有用):
CREATE OR REPLACE FUNCTION jsonb_update(val1 JSONB,val2 JSONB)
RETURNS JSONB AS $$
DECLARE
result JSONB;
v RECORD;
BEGIN
IF jsonb_typeof(val2) = 'null'
THEN
RETURN val1;
END IF;
result = val1;
FOR v IN SELECT key, value FROM jsonb_each(val2) LOOP
IF jsonb_typeof(val2->v.key) = 'object'
THEN
result = result || jsonb_build_object(v.key, jsonb_update(val1->v.key, val2->v.key));
ELSE
result = result || jsonb_build_object(v.key, v.value);
END IF;
END LOOP;
RETURN result;
END;
$$ LANGUAGE plpgsql;
下面是使用示例:
select jsonb_update('{"a":{"b":{"c":{"d":5,"dd":6},"cc":1}},"aaa":5}'::jsonb, '{"a":{"b":{"c":{"d":15}}},"aa":9}'::jsonb);
jsonb_update
---------------------------------------------------------------------
{"a": {"b": {"c": {"d": 15, "dd": 6}, "cc": 1}}, "aa": 9, "aaa": 5}
(1 row)
正如你所看到的，它深入分析并在需要的地方更新/添加值。
你觉得这个解决办法怎么样?
它将添加新值或更新现有值。
编辑:编辑后使其与null和空对象一起工作
Edit2:编辑使它与对象中的对象一起工作…
create or replace function updateJsonb(object1 json, object2 json)
returns jsonb
language plpgsql
as
$$
declare
result jsonb;
tempObj1 text;
tempObj2 text;
begin
tempObj1 = substr(object1::text, 2, length(object1::text) - 2); --remove the first { and last }
tempObj2 = substr(object2::text, 2, length(object2::text) - 2); --remove the first { and last }
IF object1::text != '{}' and object1::text != 'null' and object1::text != '[]' THEN
result = ('{' || tempObj1 || ',' || tempObj2 || '}')::jsonb;
ELSE
result = ('{' || tempObj2 || '}')::jsonb;
END IF;
return result;
end;
$$;
用法:
update table_name
set data = updatejsonb(data, '{"test": "ok"}'::json)
即使下面的函数不能满足这个请求(函数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 );
遗憾的是，我没有在文档中找到任何东西，但您可以使用一些变通方法，例如您可以编写一些扩展函数。
例如，在Python中:
CREATE or REPLACE FUNCTION json_update(data json, key text, value json)
returns json
as $$
from json import loads, dumps
if key is None: return data
js = loads(data)
js[key] = value
return dumps(js)
$$ language plpython3u
然后
update test set data=json_update(data, 'a', to_json(5)) where data->>'b' = '2';
