使用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...

当前回答

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可能更有效,但我仍然掌握了它的窍门。当我第一次尝试使用它时,我完全把弦弄乱了……

遗憾的是,我没有在文档中找到任何东西,但您可以使用一些变通方法,例如您可以编写一些扩展函数。

例如,在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';

这个解决方案是jsonb_set的替代方案,即使JSONB的列中有NULL,它也能工作。只有当对象存在时,jsonb_set才有效。

在下面的示例中,settings是用户表上的JSONB列。

 UPDATE public."Users"
 SET settings = coalesce("settings", '{}')::jsonb || '{ "KeyToSet" : "ValueToSet" }'
 WHERE id=35877;

即使下面的函数不能满足这个请求(函数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 );

下面的plpython代码片段可能会派上用场。

CREATE EXTENSION IF NOT EXISTS plpythonu;
CREATE LANGUAGE plpythonu;

CREATE OR REPLACE FUNCTION json_update(data json, key text, value text)
 RETURNS json
 AS $$
    import json
    json_data = json.loads(data)
    json_data[key] = value
    return json.dumps(json_data, indent=4)
 $$ LANGUAGE plpythonu;

-- Check how JSON looks before updating

SELECT json_update(content::json, 'CFRDiagnosis.mod_nbs', '1')
FROM sc_server_centre_document WHERE record_id = 35 AND template = 'CFRDiagnosis';

-- Once satisfied update JSON inplace

UPDATE sc_server_centre_document SET content = json_update(content::json, 'CFRDiagnosis.mod_nbs', '1')
WHERE record_id = 35 AND template = 'CFRDiagnosis';