PostgreSQLjson任意位置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)