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

当前回答

当我尝试更新字符串类型字段时,这对我有用。

UPDATE table_name 
SET body = jsonb_set(body, '{some_key}', to_json('value'::text)::jsonb);

希望它能帮助其他人!

假设表table_name有一个名为body的jsonb列,并且您想要更改body。Some_key = 'value'

其他回答

对于那些使用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字符串,

下面的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';

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

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

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

你觉得这个解决办法怎么样?

它将添加新值或更新现有值。

编辑:编辑后使其与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)

当我尝试更新字符串类型字段时,这对我有用。

UPDATE table_name 
SET body = jsonb_set(body, '{some_key}', to_json('value'::text)::jsonb);

希望它能帮助其他人!

假设表table_name有一个名为body的jsonb列,并且您想要更改body。Some_key = 'value'