PostgreSQL支持非关系数据类型json (JavaScript Object Notation)，本节介绍json类型、json与jsonb差异、json与jsonb操作符和函数，以及jsonb键值的追加、删除、更新。

JSON类型简介

PotgreSQL早在9.2版本已经提供了json类型，并且随着大版本的演进，PostgreSQL对json的支持趋于完善，例如提供更多的json函数和操作符方便应用开发，一个简单的json类型例子如下：

SELECT '{"a":1,"b":2}'::json;json---------------{"a":1,"b":2}

创建一张表，如下所示：

CREATE TABLE test_json1 (id serial primary key,name json);

以上示例定义字段name为json类型，插入表数据，如下所示：

INSERT INTO test_json1 (name)VALUES ('{"col1":1,"col2":"test","col3":"male"}');INSERT 0 1INSERT INTO test_json1 (name)VALUES ('{"col1":2,"col2":"fp","col3":"female"}');INSERT 0 1

查询表test_json1数据：

SELECT * FROM test_json1;id | name----+------------------------------------------1 | {"col1":1,"col2":"francs","col3":"male"}2 | {"col1":2,"col2":"fp","col3":"female"}————————————————

查询JSON数据

通过 -> 操作符可以查询json数据的键值，如下所示：

SELECT name -> 'col2' FROM test_json1 WHERE id=1;?column?----------"francs"(1 row)

如果想以文本格式返回json字段键值可以使用->>符，如下所示：

SELECT name ->> 'col2' FROM test_json1 WHERE id=1;?column?----------francs(1 row)

JSONB与JSON差异

PostgreSQL支持两种JSON数据类型：json和jsonb，PostgreSQL 9.4 有加入了jsonb 类型，两种类型在使用上几乎完全相同，两者主要区别为以下：

• json存储格式为文本，而jsonb存储格式为二进制 ，由于存储格式的不同使得两种json数据类型的处理效率不一样，json类型以文本存储并且存储的内容和输入数据一样，当检索json数据时必须重新解析，而jsonb以二进制形式存储已解析好的数据，当检索jsonb数据时不需要重新解析，因此json写入比jsonb快，但检索比jsonb慢，后面会通过测试验证两者读写性能差异。
• json与jsonb在使用过程中还存在差异，jsonb输出的键的顺序和输入不一样，而json的输出键的顺序和输入完全一样，如下所示：

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

• jsonb类型会去掉输入数据中键值的空格，而json的输出和输入一样，不会删掉空格键，如下所示：

CREATE TABLE books (  id SERIAL PRIMARY KEY,  client TEXT NOT NULL,  data JSONb NOT NULL);INSERT INTO books(client, data) values ( 'Joe',     '{ "title": "Siddhartha", "author": { "first_name": "Herman", "last_name": "Hesse" } }'),( 'Jenny',     '{ "title": "Dharma Bums", "author": { "first_name": "Jack", "last_name": "Kerouac" } }'),( 'Jenny',     '{ "title": "100 años de soledad", "author": { "first_name": "Gabo", "last_name": "Marquéz" } }');SELECT * FROM books;

jsonb去除不必要的空格

CREATE TABLE books_1 (  id SERIAL PRIMARY KEY,  client TEXT NOT NULL,  data JSONb NOT NULL);INSERT INTO books_1(client, data) values ( 'Joe',     '{ "title": "Siddhartha", "author": { "first_name": "Herman", "last_name": "Hesse" } }'),( 'Jenny',     '{ "title": "Dharma Bums", "author": { "first_name": "Jack", "last_name": "Kerouac" } }'),( 'Jenny',     '{ "title": "100 años de soledad", "author": { "first_name": "Gabo", "last_name": "Marquéz" } }');SELECT * FROM books_1;

json保留空格

• jsonb会删除重复的键，仅保留最后一个，，而json数据类型会保留重复的键值，如下所示：

SELECT ' {"id":1,"name":"francs","remark":"a good guy!","name":"test"}'::jsonb;jsonb----------------------------------------------------{"id": 1, "name": "test", "remark": "a good guy!"}(1 row)

相比json大多数应用场景建议使用jsonb，除非有特殊的需求，比如对json的键顺序有特殊的要求。

JSONB与JSON操作符

PostgreSQL支持丰富的JSONB和JSON的操作符，举例如下：-> 以json对象形式返回， ->>以文本格式返回，如下所示：

select data -> 'title' as title from books;

-> 返回json对象

select data -> 'author' ->> 'last_name' as last_name from books;

->>返回文本

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

jsonb键/值追加可通过||操作符，如下增加sex键/值：

select data || '{"sex":"male"}'::jsonb FROM books

追加key

jsonb键/值的删除有两种方法，一种是通过操作符号-删除，通过操作符号-删除键/值如下：

SELECT '{"name": "James", "email": "james@localhost"}'::jsonb- 'email';?column?-------------------{"name": "James"}(1 row)SELECT '["red","green","blue"]'::jsonb - 0;?column?-------------------["green", "blue"]

第二种方法是通过操作符#-删除指定键/值，通常用于有嵌套json数据删除的场景，如下删除嵌套contact中的fax键/值：

SELECT '{"name": "James", "contact": {"phone": "01234 567890", "fax": "01987 543210"}}'::jsonb #- '{contact,fax}'::text[];?column?---------------------------------------------------------{"name": "James", "contact": {"phone": "01234 567890"}}(1 row)

删除嵌套aliases中的位置为1的键/值，如下所示：

SELECT '{"name": "James", "aliases": ["Jamie","The Jamester","J Man"]}'::jsonb #- '{aliases,1}'::text[];?column?--------------------------------------------------{"name": "James", "aliases": ["Jamie", "J Man"]}(1 row)

键/值的更新也有两种方式，第一种方式为||操作符，||操作符可以连接json键，也可覆盖重复的键值，如下修改age键的值：

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表示如果键不存在则添加， false表示如果键不存在则不添加，默认值为true

示例如下：

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);jsonb_set------------------------------------------------{"age": "31", "sex": "male", "name": "francs"}(1 row)

给JSONB类型创建索引

给jsonb数据类型创建索引，jsonb数据类型支持GIN索引，为了便于说明，假如一个json字段内容如下，并且以jsonb格式存储。

{  "id": 1,  "user_id": 1440933,  "user_name": "1_francs",  "create_time": "2023-02-03 16:22:05.528432+08"}

假如存储以上jsonb数据的字段名为user_info，表名为tbl_user_jsonb，在user_info字段上创建GIN索引语法如下：

CREATE INDEX idx_gin ON tbl_user_jsonb USING gin(user_info);

jsonb上的GIN索引支持@>、?、 ?&、?|操作符，例如以下查询将会使用索引。

SELECT * FROM tbl_user_jsonb WHERE user_info @> '{"user_name": "1_frans"}'

但是以下基于jsonb键值的查询不会走索引idx_gin，如下所示：

SELECT * FROM tbl_user_jsonb WHERE user_info->>'user_name'= '1_francs';

如果要想提升基于jsonb类型的键值检索效率，可以在jsonb数据类型对应的键值上创建索引，如下所示：

CREATE INDEX idx_gin_user_infob_user_name ON tbl_user_jsonb USING btree

((user_info ->> 'user_name'));

创建以上索引后，上述根据user_info->>'user_name'键值查询的SQL将会走索引。

JSON与JSONB读写性能测试

本小节将对json、jsonb读写性能进行简单对比，前面介绍json、jsonb数据类型时提到了两者读写性能的差异，主要表现为json写入时比jsonb快，但检索时比jsonb慢，主要原因为：json存储格式为文本而jsonb存储格式为二进制，存储格式的不同使得两种json数据类型的处理效率不一样，json类型存储的内容和输入数据一样，当检索json数据时必须重新解析，而jsonb以二进制形式存储已解析好的数据，当检索jsonb数据时不需要重新解析。

准备测试数据

下面通过一个简单的例子测试下json、jsonb的读写性能差异，计划创建以下三张表：

`user_ini：基础数据表，并插入200万测试数据；

tbl_user_json:： json 数据类型表，200万数据；

tbl_user_jsonb： jsonb 数据类型表，200万数据；

首先创建user_ini表并插入200万测试数据，如下：

CREATE TABLE user_ini(id int4 ,user_id int8, user_name charactervarying(64),create_time timestamp(6) with time zone defaultclock_timestamp());INSERT INTO user_ini(id,user_id,user_name)SELECT r,round(random()*2000000), r || '_francs'FROM generate_series(1,2000000) as r;

计划使用user_ini表数据生成json、jsonb数据，创建user_ini_json、user_ini_jsonb表，如下所示：

CREATE TABLE tbl_user_json(id serial, user_info json);CREATE TABLE tbl_user_jsonb(id serial, user_info jsonb);

JSON与JSONB表写性能测试

根据user_ini数据通过row_to_json函数向表user_ini_json插入200万json数据，如下：

INSERT INTO tbl_user_json(user_info) SELECT row_to_json(user_ini) FROM user_ini;

json所用时间

从以上结果看出tbl_user_json插入200万数据花了13秒左右；接着根据user_ini表数据生成200万jsonb数据并插入表tbl_user_jsonb，如下：

INSERT INTO tbl_user_jsonb(user_info) SELECT row_to_json(user_ini)::jsonb FROM user_ini;

jsonb所用时间

从以上看出tbl_user_jsonb表插入200万jsonb数据花了20秒左右，正好验证了json数据写入比jsonb快。

比较两表占用空间大小，如下所示：

占用空间对比

从占用空间来看，同样的数据量jsonb数据类型占用空间比json稍大。

查询测试

对于json、jsonb读性能测试我们选择基于json、jsonb键值查询的场景，例如，根据user_info字段的user_name键的值查询，如下所示：

慢查询结果

上述SQL执行时间为186毫秒左右，基于user_info字段的user_name键值创建btree索引如下：

CREATE INDEX idx_jsonb ON tbl_user_jsonb USING btree ((user_info->>'user_name'));

再次执行上述查询，如下所示：

慢查询结果

根据上述执行计划看出走了索引，并且SQL时间下降到0.147ms。为更好的对比tbl_user_json、tbl_user_jsonb表基于键值查询的效率，计划根据user_info字段id键进行范围扫描对比性能，创建索引如下：

 CREATE INDEX idx_gin_user_info_id ON tbl_user_json USING btree (((user_info ->> 'id')::integer));CREATE INDEX idx_gin_user_infob_id ON tbl_user_jsonb USING btree (((user_info ->> 'id')::integer));

慢查询结果

根据以上看出，查询表tbl_user_json的user_info字段id键值在1到10000范围内的记录走了索引，并且执行时间为27毫秒，而tbl_user_jsonb的执行时间为13毫秒，从这个测试看出jsonb检索比json效率高。

从以上两个测试看出，正好验证了“json写入比jsonb快，但检索时比jsonb慢”的观点，值得一提的是如果需要通过key/value进行检索，例如以下。

SELECT * FROM tbl_user_jsonb WHERE user_info @> '{"user_name": "2_test"}';

这时执行计划为全表扫描，如下所示：

慢查询结果

从以上看出执行时间为582毫秒左右，在tbl_user_jsonb字段user_info上创建gin索引，如下所示：

CREATE INDEX idx_tbl_user_jsonb_user_Info ON tbl_user_jsonb USING gin (user_Info);

索引创建后，再次执行以下，如下所示：

慢查询结果

从以上看出走了索引，并且执行时间下降到了0.185毫秒。

JSONB与JSON函数

row_to_json()

能够将行作为json对象返回，此函数常用来生成json测试数据，比如将一个普通表转换成json类型表：

SELECT row_to_json(books) from books

行作为json对象返回

json_each()

json与jsonb相关的函数非常丰富，举例如下： 扩展最外层的json对象成为一组键/值结果集，如下所示：

-- 键/值结果集select jsonb_each(data) from books where id =1-- 文本形式返select jsonb_each_text(data) from books where id =1

jsonb_each(column)

jsonb_each_text(column)

json_object_keys()

返回最外层的json对像中的键的集合，如下所示：

jsonb_object_keys(column)

update 待更新表 aset 待更新表_字段一 = b.字段一 ,待更新表_字段二 = b.字段二from 关联表 b where  a.关联字段= b.关联字段

eg:

update test001 a set name= b.name,age=b.agefrom test002 b where  a.id= b.id

row_to_json()

将表的所有字段值以json的形式返回，如下所示：

将表数据以json形式返回

但是有时候我们只需要查询指定的列，那么我们可以使用 row() 结构函数，如下所示：

指定列返回

虽然返回了指定列，但是缺遗失了字段名称，如需达到这样的效果，如下所示：

带字段名称返回json