PostgreSQL json 任意位置 append 功能实现

标签

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)