PostgreSQL jsonb 數組的增刪改查

有時候我們需要使用 PostgreSQL 這種結構化數組來存儲一些非結構化數據，PostgreSQL 恰好又提供了 json 這種數據類型，這裏我們來簡單介紹使用 jsonb 的一些常見操作。

PostgreSQL 提供了 json 和 jsonb 兩種 json 類型，兩者的主要區別就是，json 查詢相對慢一些，插入會快一點，而 jsonb 則相反，查詢效率會高一點，插入會慢一點。

下面進入我們今天的正題

表結構

create table purchase_order
(
    id                     serial not null primary key,
    tag                    jsonb
)

數據也是非常簡單

INSERT INTO plutus.purchase_order (id, tag) VALUES (4787, '[{"uid": 1, "name": "標籤名1", "add_time": "2021-05-29 17:00:00"}, {"uid": 2, "name": "標籤名2", "add_time": "2021-05-29 17:00:00"}]');

重點是 tag

[{
 "uid": 1,
 "name": "標籤名1",
 "add_time": "2021-05-29 17:00:00"
}, {
 "uid": 2,
 "name": "標籤名2",
 "add_time": "2021-05-29 17:00:00"
}]

操作

這裏來實現一些簡單的操作

條件查詢

我們查詢 id = 4787 且 json 中 uid = 1 的 tag

-- 嵌套子查詢
select * from (
select jsonb_array_elements(tag) as tt from purchase_order where id =  4787) a
where  tt -> 'uid' = '1';

-- 或者這種方式
SELECT
    id,r
FROM
    purchase_order s, jsonb_array_elements(s.tag) r
WHERE
    s.id =  4787  and r->>'uid' = '2' ;

查詢結果如果僅僅是查詢 json 中包含 uid = 3 的結果可以像這樣查詢

SELECT tag FROM purchase_order
WHERE id =  4787 and tag @> '[{"uid": 3}]';

新增

新增也比較簡單，我們在原有的 json 上在增加個對象。原先的 json 對象是這樣的

[{
 "uid": 1,
 "name": "標籤名1",
 "add_time": "2021-05-29 17:00:00"
}, {
 "uid": 2,
 "name": "標籤名2",
 "add_time": "2021-05-29 17:00:00"
}]

執行如下語句

UPDATE purchase_order SET tag = tag || '[{
 "uid": 3,
 "name": "標籤名3",
 "add_time": "2021-05-29 17:00:00"
}]' where  id = 4787;

再查詢

[{
 "uid": 1,
 "name": "標籤名1",
 "add_time": "2021-05-29 17:00:00"
}, {
 "uid": 2,
 "name": "標籤名2",
 "add_time": "2021-05-29 17:00:00"
}, {
 "uid": 3,
 "name": "標籤名3",
 "add_time": "2021-05-29 17:00:00"
}]

刪除

如果我要刪除上面 uid 爲 3 的值，使用如下 sql 即可

UPDATE purchase_order
       SET tag = t.js_new
      FROM
          (
           SELECT jsonb_agg( (tag ->> ( idx-1 )::int)::jsonb ) AS js_new
             FROM purchase_order
            CROSS JOIN jsonb_array_elements(tag)
             WITH ORDINALITY arr(j,idx)
            WHERE j->>'uid' NOT IN ('3')
          ) t;

再次查詢

修改

我們現在要把 id = 4787``name = '標籤名1'的修改爲name = '標籤new'

sql 如下

UPDATE purchase_order AS g
   SET tag = REPLACE(tag::text, '"name": "標籤名1"','"name": "標籤new"')::json
 WHERE g.tag IN ( SELECT g.tag
                     FROM purchase_order AS g
                    CROSS JOIN jsonb_array_elements(g.tag) AS j
                    WHERE id = 4787 and  j ->>'uid' = '1' )

總結

其實還有很多其他方式去實現，但是總體來說修改刪除都不是特別方便和好維護，建議修改刪除的時候直接當做字符串去全量更新即可，這樣好維護一點。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/cblVaXN7iYgIiuQyJyxibg