TDSQL PostgreSQL版 json 和 jsonb 的使用

一、创建 json 类型字段表

postgres=# create table t_json(id int,f_json json);NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLE

二、插入数据

postgres=# insert into t_json values(1,'{"col1":1,"col2":"tdsql_pg"}');INSERT 0 1postgres=# insert into t_json values(2,'{"col1":1,"col2":"tdsql_pg","col3":"pgxz"}');INSERT 0 1postgres=# select * from t_json; id | f_json —-+—————————————– 1 | {"col1":1,"col2":"tdsql_pg"} 2 | {"col1":1,"col2":"tdsql_pg","col3":"pgxz"}(2 rows)

三、通过键获得 json 对象域

postgres=# select f_json ->'col2' as col2 ,f_json -> 'col3' as col3 from t_json; col2 | col3 ———+——– "tdsql_pg" | "tdsql_pg" | "pgxz"(2 rows)

四、以文本形式获取对象值

postgres=# select f_json ->>'col2' as col2 ,f_json ->> 'col3' as col3 from t_json; col2 | col3 ——-+—— tdsql_pg | tdsql_pg | pgxz(2 rows)postgres=# select f_json ->>'col2' as col2 ,f_json ->> 'col3' as col3 from t_json where f_json ->> 'col3' is not null; col2 | col3 ——-+—— tdsql_pg | pgxz(1 row)

五、创建 jsonb 类型字段表

postgres=# create table t_jsonb(id int,f_jsonb jsonb);NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.CREATE TABLEpostgres=#

六、插入数据

postgres=# insert into t_jsonb values(1,'{"col1":1,"col2":"tdsql_pg"}');INSERT 0 1postgres=# insert into t_jsonb values(2,'{"col1":1,"col2":"tdsql_pg","col3":"pgxz"}');INSERT 0 1postgres=# select * from t_jsonb; id | f_jsonb —-+———————————————- 1 | {"col1": 1, "col2": "tdsql_pg"} 2 | {"col1": 1, "col2": "tdsql_pg", "col3": "pgxz"}(2 rows)

jsonb 插入时会移除重复的键，如下所示。

postgres=# insert into t_jsonb values(3,'{"col1":1,"col2":"tdsql_pg","col2":"pgxz"}');INSERT 0 1postgres=# select * from t_jsonb; id | f_jsonb —-+———————————————- 1 | {"col1": 1, "col2": "tdsql_pg"} 3 | {"col1": 1, "col2": "pgxz"} 2 | {"col1": 1, "col2": "tdsql_pg", "col3": "pgxz"}(3 rows)

七、更新数据

增加元素。

postgres=# update t_jsonb set f_jsonb = f_jsonb || '{"col3":"pgxz"}'::jsonb where id=1; UPDATE 1

更新原来的元素。

postgres=# update t_jsonb set f_jsonb = f_jsonb || '{"col2":"tdsql_pg"}'::jsonb where id=3; UPDATE 1postgres=# select * from t_jsonb; id | f_jsonb —-+———————————————- 2 | {"col1": 1, "col2": "tdsql_pg", "col3": "pgxz"} 1 | {"col1": 1, "col2": "tdsql_pg", "col3": "pgxz"} 3 | {"col1": 1, "col2": "tdsql_pg"}(3 rows)

删除某个键。

postgres=# update t_jsonb set f_jsonb = f_jsonb – 'col3'; UPDATE 3postgres=# select * from t_jsonb; id | f_jsonb —-+—————————— 2 | {"col1": 1, "col2": "tdsql_pg"} 1 | {"col1": 1, "col2": "tdsql_pg"} 3 | {"col1": 1, "col2": "tdsql_pg"}(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 表示如果键不存在则不添加。

postgres=# update t_jsonb set f_jsonb = jsonb_set( f_jsonb , '{col}' , '"pgxz"' , true ) where id=1;UPDATE 1postgres=# update t_jsonb set f_jsonb = jsonb_set( f_jsonb , '{col}' , '"pgxz"' , false ) where id=2; UPDATE 1postgres=# update t_jsonb set f_jsonb = jsonb_set( f_jsonb , '{col2}' , '"pgxz"' , false ) where id=3; UPDATE 1postgres=# select * from t_jsonb; id | f_jsonb —-+——————————————— 1 | {"col": "pgxz", "col1": 1, "col2": "tdsql_pg"} 2 | {"col1": 1, "col2": "tdsql_pg"} 3 | {"col1": 1, "col2": "pgxz"}(3 rows)

九、jsonb_each() 将 json 对象转变键和值

postgres=# select f_jsonb from t_jsonb where id=1; f_jsonb ——————————————— {"col": "pgxz", "col1": 1, "col2": "tdsql_pg"}(1 row)postgres=# select * from jsonb_each((select f_jsonb from t_jsonb where id=1)); key | value ——+——— col | "pgxz" col1 | 1 col2 | "tdsql_pg"(3 rows)

十、jsonb_each_text() 将 json 对象转变文本类型的键和值

postgres=# select * from jsonb_each_text((select f_jsonb from t_jsonb where id=1)); key | value ——+——- col | pgxz col1 | 1 col2 | tdsql_pg(3 rows)

十一、row_to_json() 将一行记录变成一个 json 对象

postgres=# \\d+ tdsql_pg Table "public.tdsql_pg" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ———-+———+———–+———-+———+———-+————–+————- id | integer | | not null | | plain | | nickname | text | | | | extended | | Indexes: "tdsql_pg_pkey" PRIMARY KEY, btree (id)Distribute By: SHARD(id)Location Nodes: ALL DATANODESpostgres=# select * from tdsql_pg; id | nickname —-+———- 1 | tdsql_pg 2 | pgxz(2 rows)postgres=# select row_to_json(tdsql_pg) from tdsql_pg; row_to_json —————————– {"id":1,"nickname":"tdsql_pg"} {"id":2,"nickname":"pgxz"}(2 rows)

十二、json_object_keys() 返回一个对象中所有的键

postgres=# select * from json_object_keys((select f_jsonb from t_jsonb where id=1)::json); json_object_keys —————— col col1 col2(3 rows)

十三、jsonb 索引使用

tdsql_pg 为文档 jsonb 提供了 GIN 索引，GIN 索引可以被用来有效地搜索在大量 jsonb 文档（数据）中出现的键或者键值对。

十四、创建 jsonb 索引

十五、测试查询的性能

postgres=# select count(1) from t_jsonb; count ———- 10000000(1 row)postgres=# analyze t_jsonb;ANALYZE

没有索引开销。

postgres=# 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)

有索引开销。