postgresql 数据库 json、jsonb 使用之一

os: centos 7.4.1708
db: postgresql 10.11

json 数据类型存储输入文本的精准拷贝，处理函数必须在每次执行时必须重新解析该数据。
jsonb数据被存储在一种分解好的 二进制格式中，它在输入时要稍慢一些，因为需要做附加的转换。
但是 jsonb在处理时要快很多，因为不需要解析。
jsonb也支持索引。

版本

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
# 
# 
# yum list installed |grep -i postgresql
postgresql10.x86_64                10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-contrib.x86_64        10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-debuginfo.x86_64      10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-devel.x86_64          10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-docs.x86_64           10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-libs.x86_64           10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-odbc.x86_64           12.00.0000-1PGDG.rhel7              @pgdg10  
postgresql10-plperl.x86_64         10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-plpython.x86_64       10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-pltcl.x86_64          10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-server.x86_64         10.11-2PGDG.rhel7                   @pgdg10  
postgresql10-tcl.x86_64            2.4.0-1.rhel7                       @pgdg10  
postgresql10-tcl-debuginfo.x86_64  2.3.1-1.rhel7                       @pgdg10  
postgresql10-test.x86_64           10.11-2PGDG.rhel7                   @pgdg10 

# su - postgres
Last login: Wed Jan 15 18:34:12 CST 2020 on pts/0
$
$
$ psql -c "select version();"
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 10.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

初步使用

postgres=# SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;
                       json                       
--------------------------------------------------
 {"bar": "baz", "balance": 7.77, "active": false}
(1 row)

postgres=# SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::jsonb;
                      jsonb                       
--------------------------------------------------
 {"bar": "baz", "active": false, "balance": 7.77}
(1 row)

postgres=# create table tmp_t0(
	id bigint primary key,
	name_varchar varchar(100),
	name_json    json,
	name_jsonb   jsonb
);

postgres=# \d+ tmp_t0
                                             Table "public.tmp_t0"
    Column    |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------------+------------------------+-----------+----------+---------+----------+--------------+-------------
 id           | bigint                 |           | not null |         | plain    |              | 
 name_varchar | character varying(100) |           |          |         | extended |              | 
 name_json    | json                   |           |          |         | extended |              | 
 name_jsonb   | jsonb                  |           |          |         | extended |              | 
Indexes:
    "tmp_t0_pkey" PRIMARY KEY, btree (id)


postgres=# insert into tmp_t0
SELECT 1 as id,
       '{"bar": "baz", "balance": 7.77, "active": false}',
	   '{"bar": "baz", "balance": 7.77, "active": false}'::json,
       '{"bar": "baz", "balance": 7.77, "active": false}'::jsonb;

postgres=# select * from tmp_t0;
 id |                   name_varchar                   |                    name_json                     |                    name_jsonb                    
----+--------------------------------------------------+--------------------------------------------------+--------------------------------------------------
  1 | {"bar": "baz", "balance": 7.77, "active": false} | {"bar": "baz", "balance": 7.77, "active": false} | {"bar": "baz", "active": false, "balance": 7.77}
(1 row)

提取 active 值

postgres=# explain select * from tmp_t0 where name_jsonb -> 'active' = 'false';
                         QUERY PLAN                          
-------------------------------------------------------------
 Seq Scan on tmp_t0  (cost=0.00..13.75 rows=1 width=290)
   Filter: ((name_jsonb -> 'active'::text) = 'false'::jsonb)
(2 rows)

postgres=# select * from tmp_t0 where name_jsonb -> 'active' = 'false';
 id |                   name_varchar                   |                    name_json                     |                    name_jsonb                    
----+--------------------------------------------------+--------------------------------------------------+--------------------------------------------------
  1 | {"bar": "baz", "balance": 7.77, "active": false} | {"bar": "baz", "balance": 7.77, "active": false} | {"bar": "baz", "active": false, "balance": 7.77}
(1 row)

在 jsonb 字段上创建高效索引

postgres=# CREATE INDEX idx_tmp_t0_name_jsonb ON tmp_t0 USING gin (name_jsonb);

postgres=# CREATE INDEX idx_tmp_t0_name_jsonb_active ON tmp_t0 USING gin (( name_jsonb -> 'active' ));

参考:
http://postgres.cn/docs/10/datatype-json.html
http://postgres.cn/docs/10/functions-json.html