使用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)
正如你所看到的，它深入分析并在需要的地方更新/添加值。
遗憾的是，我没有在文档中找到任何东西，但您可以使用一些变通方法，例如您可以编写一些扩展函数。
例如，在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';
对于那些使用mybatis的人，这里有一个更新语句示例:
<update id="saveAnswer">
update quiz_execution set answer_data = jsonb_set(answer_data, concat('{', #{qid}, '}')::text[], #{value}::jsonb), updated_at = #{updatedAt}
where id = #{id}
</update>
参数:
Qid, field的键。
是一个有效的json字符串，对于字段值，
通过jackson将对象转换为json字符串，
select * from pg_settings where name = 'deadlock_timeout';
begin;
create temp table a2(data jsonb);
insert into a2 values('{
"key0": {
"key01": "2018-05-06T12:36:11.916761+00:00",
"key02": "DEFAULT_WEB_CONFIGURATION",
"key1": {
"key11": "Data System",
"key12": "<p>Health,<p>my address<p>USA",
"key13": "*Please refer to main screen labeling"
}
}}'::jsonb);
commit;
嵌套的jsonb结构更新。可应用于删除。
update a2 set data =
data::jsonb #- '{key0, key1, key13}'
|| '{"key13":"screen labeling"}'::jsonb
returning *;
例如，我的字符串是这样的:
{" a1 ":{“a11”:“x”,“a22”:“y”、“a33”:“z”}}
我使用临时表更新jsons，这对于相当小的数据量(<1.000.000)已经足够了。我找到了另一条路，但后来去度假了，就忘了……
所以。查询将是这样的:
with temp_table as (
select
a.id,
a->'a1'->>'a11' as 'a11',
a->'a1'->>'a22' as 'a22',
a->'a1'->>'a33' as 'a33',
u1.a11updated
from foo a
join table_with_updates u1 on u1.id = a.id)
update foo a
set a = ('{"a1": {"a11": "'|| t.a11updated ||'",
"a22":"'|| t.a22 ||'",
"a33":"'|| t.a33 ||'"}}')::jsonb
from temp_table t
where t.id = a.id;
它与string的关系比json更大，但它是有效的。基本上，它将所有数据拉到临时表中，在用备份的数据插入concat漏洞时创建一个字符串，并将其转换为jsonb。
Json_set可能更有效，但我仍然掌握了它的窍门。当我第一次尝试使用它时，我完全把弦弄乱了……
