快捷導(dǎo)航

PostgreSQL使用jsonb進(jìn)行數(shù)組增刪改查的操作詳解

更新時(shí)間：2024年03月15日 11:29:18 作者：weihubeats

有時(shí)候我們需要使用PostgreSQL這種結(jié)構(gòu)化數(shù)據(jù)庫(kù)來(lái)存儲(chǔ)一些非結(jié)構(gòu)化數(shù)據(jù),PostgreSQL恰好又提供了json這種數(shù)據(jù)類型,這里我們來(lái)簡(jiǎn)單介紹使用jsonb的一些常見(jiàn)操作,需要的朋友可以參考下

表結(jié)構(gòu)

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

數(shù)據(jù)也是非常簡(jiǎn)單

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

重點(diǎn)是tag

[{
	"uid": 1,
	"name": "標(biāo)簽名1",
	"add_time": "2021-05-29 17:00:00"
}, {
	"uid": 2,
	"name": "標(biāo)簽名2",
	"add_time": "2021-05-29 17:00:00"
}]

操作

這里來(lái)實(shí)現(xiàn)一些簡(jiǎn)單的操作

條件查詢

我們查詢 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' ;

查詢結(jié)果

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

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

新增

新增也比較簡(jiǎn)單，我們?cè)谠械膉son上在增加個(gè)對(duì)象。
原先的json對(duì)象是這樣的

[{
	"uid": 1,
	"name": "標(biāo)簽名1",
	"add_time": "2021-05-29 17:00:00"
}, {
	"uid": 2,
	"name": "標(biāo)簽名2",
	"add_time": "2021-05-29 17:00:00"
}]

執(zhí)行如下語(yǔ)句

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

再查詢

[{
	"uid": 1,
	"name": "標(biāo)簽名1",
	"add_time": "2021-05-29 17:00:00"
}, {
	"uid": 2,
	"name": "標(biāo)簽名2",
	"add_time": "2021-05-29 17:00:00"
}, {
	"uid": 3,
	"name": "標(biāo)簽名3",
	"add_time": "2021-05-29 17:00:00"
}]

刪除

如果我要?jiǎng)h除上面 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;

再次查詢

修改

我們現(xiàn)在要把 id = 4787name = '標(biāo)簽名1'的修改為name = '標(biāo)簽new'

sql 如下

UPDATE purchase_order AS g
   SET tag = REPLACE(tag::text, '"name": "標(biāo)簽名1"','"name": "標(biāo)簽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' )