digoal
2018-01-05
PostgreSQL , json , 合并 , replace , append
在JSON的任意位置，插入或append一段。目前PG内置函数不支持，需要复杂的SQL。
目前支持的内置操作符和函数如下：
https://www.postgresql.org/docs/current/static/functions-json.html
现在有一个这样的需求，在对应某个PATH的位置APPEND几个值。
{"time": "1", "queue": {"digoal": {"a": 0}}}
修改为
{"time": "1", "queue": {"digoal": {"a": 0, "b": 1, "c": 2}}}
首先提取对应位置的值，然后concat，最后SET对应PATH的值，达到追加的效果。
postgres=# select jsonb_set(js, '{queue, digoal}', jsonb_extract_path(jsonb_extract_path(js, 'queue'),'digoal')||'{"b":1, "c":2}') from (values ('{"queue":{"digoal":{"a":0}} , "time": "1"}'::jsonb) ) as t (js);
jsonb_set
------------------------------------------------------------------------------
{"time": "1", "queue": {"digoal": {"a": 0, "b": 1, "c": 2}}}
(1 row)
postgres=# select js from (values ('{"queue":{"digoal":{"a":0}} , "time": "1"}'::jsonb) ) as t (js);
js
--------------------------------------------------------------
{"time": "1", "queue": {"digoal": {"a": 0}}}
(1 row)
使用函数，简化SQL，如下：
create or replace function jsonb_append(js jsonb, jsapp jsonb, text[]) returns jsonb as $$
declare
x text;
sql text := format('%L', js);
tmp jsonb;
res jsonb;
begin
foreach x in array $3 loop
sql := format ('jsonb_extract_path(%s, %L)', sql, x) ;
-- raise notice '%', sql;
end loop;
EXECUTE format('select jsonb_concat(%s, %L)', sql, jsapp) INTO tmp;
res := jsonb_set(js, $3, tmp);
return res;
end;
$$ language plpgsql strict;
以上SQL改成这样既可，第一个参数表示原始JSON，第二个参数表示需要追加的JSON，第三个参数表示位置。
postgres=# select jsonb_append(js, '{"b":1, "c":2}', '{queue, digoal}') from (values ('{"queue":{"digoal":{"a":0}} , "time": "1"}'::jsonb)) t(js);
jsonb_append
------------------------------------------------------------------------------
{"time": "1", "queue": {"digoal": {"a": 0, "b": 1, "c": 2}}}
(1 row)
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
