postgresql 数据库 json、jsonb 使用之一

ゝ一世哀愁。 2023-03-06 05:42 13阅读 0赞

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