创建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 ) where id=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
