TDSQL PostgreSQL版 json 和 jsonb 的使用-开发指南-文档中心-腾讯云

TDSQL PostgreSQL版 不只是一个分布式关系型数据库系统，同时它还支持非关系数据类型 json。json 数据类型用来存储 JSON（JavaScript Object Notation）数据。这种数据也可以被存储为 text，但是 json 数据类型的优势在于能强制要求每个被存储的值符合 json 规则。 也有很多 json 相关的函数和操作符可以用于存储在这些数据类型中的数据。
json 数据类型有 json 和 jsonb，它们接受完全相同的值集合作为输入，主要的区别是效率。json 数据类型存储输入文本的精准拷贝，处理函数必须在每次执行时必须重新解析该数据。而 jsonb 数据被存储在一种分解好的二进制格式中，它在输入时要稍慢一些，因为需要做附加的转换。但是 jsonb 在处理时要快很多，因为不需要解析。jsonb 也支持索引，这也是其优势。 
json 应用
创建 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 1
postgres=# insert into t_json values(2,'{"col1":1,"col2":"tdsql_pg","col3":"pgxz"}');
INSERT 0 1
postgres=# 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 应用
创建 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 TABLE
postgres=# 
插入数据
postgres=# insert into t_jsonb values(1,'{"col1":1,"col2":"tdsql_pg"}');
INSERT 0 1
postgres=# insert into t_jsonb values(2,'{"col1":1,"col2":"tdsql_pg","col3":"pgxz"}');
INSERT 0 1
﻿
postgres=# 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 1
postgres=# 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 1
﻿
postgres=# 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 3
﻿
postgres=# 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 1
postgres=# update t_jsonb set f_jsonb = jsonb_set( f_jsonb , '{col}' , '"pgxz"' , false ) where id=2;     
UPDATE 1
postgres=# update t_jsonb set f_jsonb = jsonb_set( f_jsonb , '{col2}' , '"pgxz"' , false ) where id=3;      
UPDATE 1
postgres=# 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 函数应用
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 DATANODES
﻿
postgres=# 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=# create index t_jsonb_f_jsonb_idx on t_jsonb using gin(f_jsonb);
CREATE INDEX
﻿
postgres=# \\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)
Distribute By: SHARD(id)
Location Nodes: ALL DATANODES
测试查询的性能
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)
有索引开销。
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: 217.968 ms
﻿
json 和 jsonb 的使用

本页目录：

json 应用

创建 json 类型字段表

插入数据

通过键获得 json 对象域

以文本形式获取对象值

jsonb 应用

创建 jsonb 类型字段表

插入数据

更新数据

jsonb_set() 函数更新数据

jsonb 函数应用

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

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

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

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

jsonb 索引使用

创建 jsonb 索引

测试查询的性能
