在PostgreSQL 9.4中,我们实现了以下Python函数。它可能也可与PostgreSQL 9.3一起使用。
create language plpython2u;
create or replace function json_set(jdata jsonb, jpaths jsonb, jvalue jsonb) returns jsonb as $$
import json
a = json.loads(jdata)
b = json.loads(jpaths)
if a.__class__.__name__ != 'dict' and a.__class__.__name__ != 'list':
raise plpy.Error("The json data must be an object or a string.")
if b.__class__.__name__ != 'list':
raise plpy.Error("The json path must be an array of paths to traverse.")
c = a
for i in range(0, len(b)):
p = b[i]
plpy.notice('p == ' + str(p))
if i == len(b) - 1:
c[p] = json.loads(jvalue)
else:
if p.__class__.__name__ == 'unicode':
plpy.notice("Traversing '" + p + "'")
if c.__class__.__name__ != 'dict':
raise plpy.Error(" The value here is not a dictionary.")
else:
c = c[p]
if p.__class__.__name__ == 'int':
plpy.notice("Traversing " + str(p))
if c.__class__.__name__ != 'list':
raise plpy.Error(" The value here is not a list.")
else:
c = c[p]
if c is None:
break
return json.dumps(a)
$$ language plpython2u ;
示例用法:
create table jsonb_table (jsonb_column jsonb);
insert into jsonb_table values
('{"cars":["Jaguar", {"type":"Unknown","partsList":[12, 34, 56]}, "Atom"]}');
select jsonb_column->'cars'->1->'partsList'->2, jsonb_column from jsonb_table;
update jsonb_table
set jsonb_column = json_set(jsonb_column, '["cars",1,"partsList",2]', '99');
select jsonb_column->'cars'->1->'partsList'->2, jsonb_column from jsonb_table;
请注意,我曾为以前的雇主编写了一组用于处理JSON数据的C函数,作为文本(而不是
json 或 jsonb 类型),适用于 PostgreSQL 7、8和9。例如,使用json_path('{"obj":[12, 34, {"num":-45.67}]}', '$.obj [2] ['num']')
提取数据,使用json_path_set('{"obj":[12, 34, {"num": -45.67}]}','$ .obj [2] ['num']','99.87')
设置数据等等。这需要大约3天的工作时间,因此,如果您需要在遗留系统上运行它并且有足够的时间,请付出努力。我想C版本比Python版本快得多。