PostgreSQL的json和jsonb比较

PostgreSQL何以支持丰富的NoSQL特性?

一、引言

PostgreSQL不仅是关系型数据库，同时支持丰富的NoSQL特性

本文主要包含以下三部分内容:

• PostgreSQL的 JSON和JSONB数据类型简介
• JSON与JSONB读写性能测试
• PostgreSQL全文检索支持JSON和JSONB（PosgreSQL 10 新特性）

二、PostgreSQL的JSON和JSONB数据类型

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

JSON类型简介

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

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

为了更好演示json类型，接下来创建一张表，如下所示：

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

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

mydb=> INSERT INTO test_json1 (name)
VALUES ('{"col1":1,"col2":"francs","col3":"male"}');
INSERT 0 1

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

查询表test_json1数据：

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

查询JSON数据

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

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

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

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

JSONB与JSON差异

PostgreSQL支持两种JSON数据类型：json和jsonb，两种类型在使用上几乎完全相同，两者主要区别为以下：json存储格式为文本而jsonb存储格式为二进制 ，由于存储格式的不同使得两种json数据类型的处理效率不一样，json类型以文本存储并且存储的内容和输入数据一样，当检索json数据时必须重新解析，而jsonb以二进制形式存储已解析好的数据，当检索jsonb数据时不需要重新解析，因此json写入比jsonb快，但检索比jsonb慢，后面会通过测试验证两者读写性能差异。

除了上述介绍的区别之外，json与jsonb在使用过程中还存在差异，例如jsonb输出的键的顺序和输入不一样，如下所示：

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

而json的输出键的顺序和输入完全一样，如下所示：

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

另外，jsonb类型会去掉输入数据中键值的空格，如下所示：

mydb=> SELECT ' {"id":1,    "name":"francs"}'::jsonb;
            jsonb
-----------------------------
 {"id": 1, "name": "francs"}
(1 row)

上例中id键与name键输入时是有空格的，输出显示空格键被删除，而json的输出和输入一样，不会删掉空格键：

mydb=> SELECT ' {"id":1,    "name":"francs"}'::json;
             json
-------------------------------
  {"id":1,    "name":"francs"}
(1 row)

另外，jsonb会删除重复的键，仅保留最后一个，如下所示：

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

上面name键重复，仅保留最后一个name键的值，而json数据类型会保留重复的键值。 相比json大多数应用场景建议使用jsonb，除非有特殊的需求，比如对json的键顺序有特殊的要求。

JSONB与JSON操作符

PostgreSQL支持丰富的JSONB和JSON的操作符，举例如下： 以文本格式返回json类型的字段键值可以使用->>符，如下所示：

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

字符串是否作为顶层键值，如下所示：

mydb=> SELECT '{"a":1, "b":2}'::jsonb ? 'a';
 ?column?
----------
 t
(1 row)

删除json数据的键/值，如下所示：

mydb=> SELECT '{"a":1, "b":2}'::jsonb - 'a';
 ?column?
----------
 {"b": 2}
(1 row)

JSONB与JSON函数

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

mydb=> SELECT * FROM json_each('{"a":"foo", "b":"bar"}');
 key | value
-----+-------
 a   | "foo"
 b   | "bar"
(2 rows)

以文本形式返回结果，如下所示：

mydb=> SELECT * FROM json_each_text('{"a":"foo", "b":"bar"}');
 key | value
-----+-------
 a   | foo
 b   | bar
(2 rows)

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

mydb=> SELECT * FROM test_copy WHERE id=1;
 id | name
----+------
  1 | a
(1 row)

mydb=> SELECT row_to_json(test_copy) FROM test_copy WHERE id=1;
     row_to_json
---------------------
 {"id":1,"name":"a"}
(1 row)

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

mydb=> SELECT * FROM json_object_keys('{"a":"foo", "b":"bar"}');
 json_object_keys
------------------
 a
 b
(2 rows)

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

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

mydb=> SELECT '{"name":"francs","age":"31"}'::jsonb ||
'{"sex":"male"}'::jsonb;
                    ?column?
------------------------------------------------
 {"age": "31", "sex": "male", "name": "francs"}