jsonb 應用
更新時間 2025-02-14 10:22:18
最近更新時間: 2025-02-14 10:22:18
分享文章
本頁介紹天翼云TeleDB數據庫jsonb應用。
創建jsonb類型字段表
teledb=# create table t_jsonb(id int,f_jsonb jsonb);
CREATE TABLE插入數據
teledb=# insert into t_jsonb values(1,'{"col1":1,"col2":"teledb"}');
INSERT 0 1
teledb=# insert into t_jsonb values(2,'{"col1":1,"col2":"teledb","col3":"pgxz"}');
INSERT 0 1
teledb=# select * from t_jsonb;
id | f_jsonb
----+------------------------------------------------
1 | {"col1": 1, "col2": "teledb"}
2 | {"col1": 1, "col2": "teledb", "col3": "pgxz"}
(2 rows)jsonb插入時會移除重復的鍵，如下所示。
teledb=# insert into t_jsonb values(3,'{"col1":1,"col2":"teledb","col2":"pgxz"}');
INSERT 0 1
teledb=# select * from t_jsonb;
id | f_jsonb
----+------------------------------------------------
1 | {"col1": 1, "col2": "teledb"}
2 | {"col1": 1, "col2": "teledb", "col3": "pgxz"}
3 | {"col1": 1, "col2": "pgxz"}
(3 rows)更新數據
增加元素。
teledb=# update t_jsonb set f_jsonb = f_jsonb || '{"col3":"pgxz"}'::jsonb where id=1;
UPDATE 1
teledb=# select * from t_jsonb;
id | f_jsonb
----+------------------------------------------------
2 | {"col1": 1, "col2": "teledb", "col3": "pgxz"}
1 | {"col1": 1, "col2": "teledb", "col3": "pgxz"}
3 | {"col1": 1, "col2": "pgxz"}
(3 rows)更新原來的元素。
teledb=# update t_jsonb set f_jsonb = f_jsonb || '{"col2":"teledb_update"}'::jsonb where id=3;
UPDATE 1
teledb=# select * from t_jsonb;
id | f_jsonb
----+------------------------------------------------
2 | {"col1": 1, "col2": "teledb", "col3": "pgxz"}
1 | {"col1": 1, "col2": "teledb", "col3": "pgxz"}
3 | {"col1": 1, "col2": "teledb_update"}
(3 rows)刪除某個鍵。
teledb=# update t_jsonb set f_jsonb = f_jsonb - 'col3';
UPDATE 3
teledb=# select * from t_jsonb;
id | f_jsonb
----+---------------------------------------
2 | {"col1": 1, "col2": "teledb"}
1 | {"col1": 1, "col2": "teledb"}
3 | {"col1": 1, "col2": "teledb_update"}
(3 rows)jsonb_set()函數更新數據
jsonb_set(target jsonb, path text[], new_value jsonb, [create_missing boolean])說明
target指要更新的數據源，path指路徑，new_value指更新后的鍵值，create_missing值為true表示如果鍵不存在則添加，create_missing值為false表示如果鍵不存在則不添加。
teledb=# update t_jsonb set f_jsonb = jsonb_set( f_jsonb , '{col}' , '"pgxz"' , true ) where id=1;
UPDATE 1
teledb=# select * from t_jsonb;
id | f_jsonb
----+-----------------------------------------------
2 | {"col1": 1, "col2": "teledb"}
3 | {"col1": 1, "col2": "teledb_update"}
1 | {"col": "pgxz", "col1": 1, "col2": "teledb"}
(3 rows)
teledb=# update t_jsonb set f_jsonb = jsonb_set( f_jsonb , '{col}' , '"pgxz"' , false ) whereid=2;
UPDATE 1
teledb=# select * from t_jsonb;
id | f_jsonb
----+-----------------------------------------------
1 | {"col": "pgxz", "col1": 1, "col2": "teledb"}
2 | {"col1": 1, "col2": "teledb"}
3 | {"col1": 1, "col2": "teledb_update"}
(3 rows)
teledb=# update t_jsonb set f_jsonb = jsonb_set( f_jsonb , '{col2}' , '"pgxz"' , false ) where id=3;
UPDATE 1
teledb=# select * from t_jsonb;
id | f_jsonb
----+-----------------------------------------------
1 | {"col": "pgxz", "col1": 1, "col2": "teledb"}
2 | {"col1": 1, "col2": "teledb"}
3 | {"col1": 1, "col2": "pgxz"}
(3 rows)jsonb 函數應用
jsonb_each() 將 json 對象轉變鍵和值
teledb=# select f_jsonb from t_jsonb where id=1;
f_jsonb
-----------------------------------------------
{"col": "pgxz", "col1": 1, "col2": "teledb"}
(1 row)
teledb=# select * from jsonb_each((select f_jsonb from t_jsonb where id=1));
key | value
------+-----------
col | "pgxz"
col1 | 1
col2 | "teledb"
(3 rows)jsonb_each_text() 將 json 對象轉變文本類型的鍵和值
teledb=# select * from jsonb_each_text((select f_jsonb from t_jsonb where id=1));
key | value
------+---------
col | pgxz
col1 | 1
col2 | teledb
(3 rows)row_to_json() 將一行記錄變成一個json對象
teledb=# create table t1(id int, name varchar);
CREATE TABLE
teledb=# insert into t1 values(1,'teledb'),(2,'pgxc');
COPY 2
teledb=# select * from t1;
id | name
----+---------
1 | teledb
2 | pgxc
(2 rows)
teledb=# select row_to_json(t1) from t1;
row_to_json
---------------------------
{"id":1,"name":"teledb"}
{"id":2,"name":"pgxc"}
(2 rows)json_object_keys()返回一個對象中所有的鍵
teledb=# select * from json_object_keys((select row_to_json(t1) from t1 where id = 1));
json_object_keys
------------------
id
name
(2 rows)
teledb=# select * from json_object_keys((select f_jsonb from t_jsonb where id=1)::json);
json_object_keys
------------------
col
col1
col2
(3 rows)jsonb 索引使用
TeleDB為文檔jsonb提供了GIN索引，GIN索引可以被用來有效地搜索在大量jsonb 文檔（數據）中出現的鍵或者鍵值對。
創建jsonb 索引
teledb=# create index t_jsonb_f_jsonb_idx on t_jsonb using gin(f_jsonb);
CREATE INDEX
teledb=# \d+ t_jsonb
Table "public.t_jsonb"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
f_jsonb | jsonb | | | | extended | |
Indexes:
"t_jsonb_f_jsonb_idx" gin (f_jsonb)
Has ROWIDs: yes
Distribute By: HASH(id)
Location Nodes: ALL DATANODES測試查詢的性能
teledb=# select count(1) from t_jsonb;
count
----------
10000000
(1 row)
teledb=# analyze t_jsonb;
ANALYZE沒有索引開銷
teledb=# select * from t_jsonb where f_jsonb @> '{"col1":9999}'; id | f_jsonb ------+-------------------------------- 9999 | {"col1": 9999, "col2": "9999"} 9999 | {"col1": 9999, "col2": "9999"} 9999 | {"col1": 9999, "col2": "9999"} 9999 | {"col1": 9999, "col2": "9999"} 9999 | {"col1": 9999, "col2": "9999"} (5 rows) Time: 2473.488 ms (00:02.473)有索引開銷
teledb=# select * from t_jsonb where f_jsonb @> '{"col1":9999}'; id | f_jsonb ------+-------------------------------- 9999 | {"col1": 9999, "col2": "9999"} 9999 | {"col1": 9999, "col2": "9999"} 9999 | {"col1": 9999, "col2": "9999"} 9999 | {"col1": 9999, "col2": "9999"} 9999 | {"col1": 9999, "col2": "9999"} (5 rows) Time: 217.968 ms
