10.数据类型-json/jsonb类型

前言

一、json/jsonb类型

PostgreSQL不只是一个关系型数据库，同时它还支持非关系数据类型json,
json属于重量级的非常规数据类型。

1.json类型简介

select '{"a":1,"b":2}'::json;
lhcdb@10.206.132.17:5432=>select '{"a":1,"b":2}'::json;
     json
---------------
 {"a":1,"b":2}
(1 row)

演示json类型，接下来创建一张表test_json1:
create table lhc.test_json1(id serial primary key,name json);

插入表数据:
insert into test_json1 (name)
values ('{"col1":1,"col2":"lian","col3":"hui"}');

lhcdb@10.206.132.17:5432=>insert into test_json1 (name)
values ('{"col1":1,"col2":"lian","col3":"hui"}');lhcdb->
INSERT 0 1


insert into test_json1 (name)
values ('{"col1":2,"col2":"LIAN","col3":"JIA"}');
lhcdb@10.206.132.17:5432=>insert into test_json1 (name)
values ('{"col1":2,"col2":"LIAN","col3":"JIA"}');lhcdb->
INSERT 0 1

查询表test_json1:
select * from test_json1;
lhcdb@10.206.132.17:5432=>select * from test_json1;
 id |                 name
----+---------------------------------------
  1 | {"col1":1,"col2":"lian","col3":"hui"}
  2 | {"col1":2,"col2":"LIAN","col3":"JIA"}
(2 rows)

2.查询json数据

通过"->"操作符可以查询json数据的键值:
select name -> 'col2' from test_json1 where id=1;
lhcdb@10.206.132.17:5432=>select name -> 'col2' from test_json1 where id=1;
 ?column?
----------
 "lian"
(1 row)

使用->>操作符，以文本格式返回json字段键值:
select name ->> 'col2' from test_json1 where id = 1;
lhcdb@10.206.132.17:5432=>select name ->> 'col2' from test_json1 where id = 1;
 ?column?
----------
 lian
(1 row)

3.jsonb与json差异

两者区别以下:
json存储格式为文本；而jsonb存储格式为二进制。
存储格式不同，使得两种json数据类型的处理效率不一样。
json类型:以文本存储并且存储的内容和输入数据一样，当检索json数据时必须重新解析。
jsonb类型:以二进制形式存储已解析好的数据，当检索jsonb数据时不需要重新解析。
因此结论:
json写入比jsonb快，但检索比jsonb慢。

json和jsonb在使用过程中还存在差异:
jsonb输出的键的顺序和输入不一样:
select '{"bar":"baz","balance":7.77,"active":false}'::jsonb;
lhcdb@10.206.132.17:5432=>select '{"bar":"baz","balance":7.77,"active":false}'::jsonb;
                      jsonb
--------------------------------------------------
 {"bar": "baz", "active": false, "balance": 7.77}
(1 row)


而json输出的键的顺序和输入完全一样:
select '{"bar":"baz","balance":7.77,"active":false}'::json;
lhcdb@10.206.132.17:5432=>select '{"bar":"baz","balance":7.77,"active":false}'::json;
                    json
---------------------------------------------
 {"bar":"baz","balance":7.77,"active":false}
(1 row)

jsonb类型会去掉输入数据中键值的空格:
select '{"id":1,    "name":"francs"}'::jsonb;
lhcdb@10.206.132.17:5432=>select '{"id":1,    "name":"francs"}'::jsonb;
            jsonb
-----------------------------
 {"id": 1, "name": "francs"}
(1 row)

json类型不会去掉输入数据中键值的空格:
select '{"id":1,    "name":"francs"}'::json;
lhcdb@10.206.132.17:5432=>select '{"id":1,    "name":"francs"}'::json;
             json
------------------------------
 {"id":1,    "name":"francs"}
(1 row)

jsonb会删除重复的键，仅保留最后一个:
select '{"id":1,"name":"francs","remark":"a good guy!","name":"test"}'::jsonb;
lhcdb@10.206.132.17:5432=>select '{"id":1,"name":"francs","remark":"a good guy!","name":"test"}'::jsonb;
                       jsonb
----------------------------------------------------
 {"id": 1, "name": "test", "remark": "a good guy!"}
(1 row)

json数据类型会保留重复的键值:
select '{"id":1,"name":"francs","remark":"a good guy!","name":"test"}'::json;
lhcdb@10.206.132.17:5432=>select '{"id":1,"name":"francs","remark":"a good guy!","name":"test"}'::json;
                             json
---------------------------------------------------------------
 {"id":1,"name":"francs","remark":"a good guy!","name":"test"}
(1 row)

4.jsonb与json操作符

以文本格式返回json类型的字段键值可以使用"->>"操作符:
select name ->> 'col2' from test_json1 where id = 1;
lhcdb@10.206.132.17:5432=>select name ->> 'col2' from test_json1 where id = 1;
 ?column?
----------
 lian
(1 row)

字符串是否作为顶层键值:
select '{"a":1,"b":2}'::jsonb ? 'a';
lhcdb@10.206.132.17:5432=>select '{"a":1,"b":2}'::jsonb ? 'a';
 ?column?
----------
 t
(1 row)

删除json数据的键/值:
select '{"a":1,"b":2}'::jsonb - 'a';
lhcdb@10.206.132.17:5432=>select '{"a":1,"b":2}'::jsonb - 'a';
 ?column?
----------
 {"b": 2}
(1 row)

5.jsonb与json函数

扩展最外层的json对象成为一组键/值结果集:
select * from json_each('{"a":"lian","b":"hui"}');
lhcdb@10.206.132.17:5432=>select * from json_each('{"a":"lian","b":"hui"}');
 key | value
-----+--------
 a   | "lian"
 b   | "hui"
(2 rows)


以文本形式返回结果:
select * from json_each_text('{"a":"foo","b":"bar"}');
lhcdb@10.206.132.17:5432=>select * from json_each_text('{"a":"foo","b":"bar"}');
 key | value
-----+-------
 a   | foo
 b   | bar
(2 rows)


将一个普通表转换成json类型的表:
select * from lhc.test_area where id = 1;
lhcdb@10.206.132.17:5432=>select * from lhc.test_area where id = 1;
 id | name | fatherid
----+------+----------
  1 | 中国 |        0
(1 row)

select row_to_json(test_area) from lhc.test_area where id = 1;
lhcdb@10.206.132.17:5432=>select row_to_json(test_area) from lhc.test_area where id = 1;
             row_to_json
-------------------------------------
 {"id":1,"name":"中国","fatherid":0}
(1 row)

返回最外层的json对象中的键的集合:
select * from json_object_keys('{"a":"foo","b":"bar"}');
lhcdb@10.206.132.17:5432=>select * from json_object_keys('{"a":"foo","b":"bar"}');
 json_object_keys
------------------
 a
 b
(2 rows)

6.jsonb键/值的追加、删除、更新

jsonb键/值追加可通过"||"操作符:
select  '{"name":"francs","age":"31"}'::jsonb||'{"sex":"male"}'::jsonb;
lhcdb@10.206.132.17:5432=>select  '{"name":"francs","age":"31"}'::jsonb||'{"sex":"male"}'::jsonb;
                    ?column?
------------------------------------------------
 {"age": "31", "sex": "male", "name": "francs"}
(1 row)


jsonb键/值的删除有两种方法:
一种是通过操作符"-"删除:
select '{"name":"James","email":"james@126.com"}'::jsonb - 'email';
lhcdb@10.206.132.17:5432=>select '{"name":"James","email":"james@126.com"}'::jsonb - 'email';
     ?column?
-------------------
 {"name": "James"}
(1 row)


select '["red","green","blue"]'::jsonb - 0;
lhcdb@10.206.132.17:5432=>select '["red","green","blue"]'::jsonb - 0;
     ?column?
-------------------
 ["green", "blue"]
(1 row)


另一种通过操作符"#-"删除指定键/值:
删除嵌套aliases中的位置为1的键值:
select '{"name":"James","aliases":["James","The Jamester","J Man"]}'::jsonb #- '{aliases,1}'::text[];
lhcdb@10.206.132.17:5432=>select '{"name":"James","aliases":["James","The Jamester","J Man"]}'::jsonb #- '{aliases,1}'::text[];
                     ?column?
--------------------------------------------------
 {"name": "James", "aliases": ["James", "J Man"]}
(1 row)

删除嵌套contact中的fax键值:
select '{"name":"James","contact":{"phone":"01234 567890","fax":"01987 543210"}}'::jsonb #- '{contact,fax}'::text[];
lhcdb@10.206.132.17:5432=>select '{"name":"James","contact":{"phone":"01234 567890","fax":"01987 543210"}}'::jsonb #- '{contact,fax}'::text[];
                        ?column?
---------------------------------------------------------
 {"name": "James", "contact": {"phone": "01234 567890"}}
(1 row)


jsonb键/值的更新也有两种方法:
第一种方式为"||"操作符，"||"操作符可以连接json键，也可以覆盖重复的键值。
修改age键的值:
select '{"name":"francs","age":"31"}'::jsonb || '{"age":"32"}'::jsonb;
lhcdb@10.206.132.17:5432=>select '{"name":"francs","age":"31"}'::jsonb || '{"age":"32"}'::jsonb;
            ?column?
---------------------------------
 {"age": "32", "name": "francs"}
(1 row)


第二种方式是通过jsonb_set函数:
jsonb_set(target jsonb,path text[],new_value jsonb[,create_missing boolean]);

target指源jsonb數據，path指路徑，new_value指更新後的鍵值，
create_missing值為true表示如果键不存在则添加，
create_missing值為false表示如果键不存在则不添加。

select jsonb_set('{"name":"francs","age":"31"}'::jsonb,'{age}','"32"'::jsonb,false);
lhcdb@10.206.132.17:5432=>select jsonb_set('{"name":"francs","age":"31"}'::jsonb,'{age}','"32"'::jsonb,false);
            jsonb_set
---------------------------------
 {"age": "32", "name": "francs"}
(1 row)


select jsonb_set('{"name":"francs","age":"31"}'::jsonb,'{sex}','"male"'::jsonb,true);
lhcdb@10.206.132.17:5432=>select jsonb_set('{"name":"francs","age":"31"}'::jsonb,'{sex}','"male"'::jsonb,true);
                   jsonb_set
------------------------------------------------
 {"age": "31", "sex": "male", "name": "francs"}
(1 row)