下载掌阅APP，畅读海量书库
立即打开
JSON数据类型可以用来存储JSON（JavaScript Object Notation）数据，而JSON数据格式是在RFC 4627中定义的。当然也可以使用text、varchar等类型来存储JSON数据，但使用这些通用的字符串格式将无法自动检测字符串是否为合法的JSON数据。而且，JSON数据类型还可以使用丰富的函数。
JSON数据类型是从PostgreSQL 9.3版本开始提供的，在9.3版本中只有一种类型JSON，在PostgreSQL 9.4版本中又提供了一种更高效的类型JSONB，这两种类型在使用上几乎完全一致，两者主要区别是，JSON类型是把输入的数据原封不动地存放到数据库中（当然在存储前会做JSON的语法检查），使用时需要重新解析数据，而JSONB类型是在存储时就把JSON解析成二进制格式，使用时就无须再次解析，所以JSONB在使用时性能会更高。另外，JSONB支持在其上建索引，而JSON则不支持，这是JSONB类型一个很大的优点。
因为JSON类型是把输入的整个字符串原封不改动地保存到数据库中，因此JSON串中key之间多余的空格也会被保留下来。而且，如果JSON串中有重复的key，这些重复的key也会保留下来（默认处理时以最后一个为准），同时也会保留输入时JSON串中各个key的顺序。而JSONB类型则恰恰相反，既不会保留多余的空格，也不会保留key的顺序和重复的key。
PostgreSQL中每个数据库只允许用一种服务器编码，如果数据库的编码不是UTF-8，PostgreSQL中的JSON类型是无法严格遵循JSON规范中对字符集的要求的。如果输入中包含无法在服务器编码中表示的字符数据，将无法导入数据库中。但是，能在服务器编码中表示的非UTF-8字符则是被允许的。可以使用\uXXXX形式的转义，从而忽视数据库的字符集编码。
当把一个JSON类型的字符串转换成JSONB类型时，JSON字符串内的数据类型实际上被转换成了PostgreSQL数据库中的类型，两者的映射关系见表5-30。需要注意的是，如果是在JSONB中，不能输入超出PostgreSQL的numeric数据类型范围的值。
表5-30 JSON类型与PostgreSQL数据库类型的映射
这里举例来说明JSON类型的使用方法。
首先来看单个值的示例，具体如下：
osdba=# select '9'::json, '"osdba"'::json, 'true'::json, 'null'::json; json | json | json | json ------+---------+------+------ 9 | "osdba" | true | null (1 row)
当然也可以使用把类型名放在单引号的字符串前面的格式，示例如下：
osdba=# select json '"osdba"', json '9', json 'true', json 'null'; json | json | json | json ---------+------+------+------ "osdba" | 9 | true | null (1 row)
使用JSONB类型也一样，示例如下：
osdba=# select jsonb '"osdba"', jsonb '9', jsonb 'true', jsonb 'null'; jsonb | jsonb | jsonb | jsonb ---------+-------+-------+------- "osdba" | 9 | true | null (1 row)
JSON中使用数组的示例如下：
osdba=# SELECT '[9, true, "osdba", null]'::json, '[9, true, "osdba", null]'::jsonb;
json | jsonb
--------------------------+--------------------------
[9, true, "osdba", null] | [9, true, "osdba", null]
(1 row)
使用字典的示例如下：
osdba=# SELECT json '{"name": "osdba", "age": 40, "sex": true, "money" : 250.12}';
json
-------------------------------------------------------------
{"name": "osdba", "age": 40, "sex": true, "money" : 250.12}
(1 row)
输入带小数点的示例如下：
osdba=# select json '{"p" : 1.6735777674525e-27}';
json
-----------------------------
{"p" : 1.6735777674525e-27}
(1 row)
osdba=# select jsonb '{"p" : 1.6735777674525e-27}';
jsonb
---------------------------------------------------
{"p": 0.0000000000000000000000000016735777674525}
(1 row)
从上面的示例中可以看出，JSONB类型内部存储的是numeric类型，而不再是浮点数。
JSON类型和JSONB类型支持的操作符如表5-31所示。
表5-31 JSON类型及JSONB类型支持的操作符
还有一些操作符仅可用于JSONB类型，这部分操作符见表5-32。
表5-32 仅适用于JSONB类型的操作符
PostgreSQL中提供了很多用于创建、操作JSON类型数据的函数。
用于创建JSON类型数据的函数见表5-33。
表5-33 JSON类型的创建函数
在表5-33中，array_to_json和row_to_json除了可以指定一个pretty_bool的参数用其美化JSON的格式之外，其功能与to_json函数完全一样。
上述创建JSON的函数只能创建出JSON类型的JSON对象，而不能创建出JSONB类型的JSON对象，但实际上可以用类型转换把JSON类型的对象转换成JSONB类型。
操作JSON类型和JSONB类型数据的函数见表5-34。
表5-34 JSON/JSONB类型数据的处理函数
表5-34中以“jsonb_path”开头的几个函数是PostgreSQL 12版本中开始加入的，同时增加了数据类型jsonpath，jsonpath是一个功能非常强大的表达式，称为“SQL/JSON Path Language”，具体的使用方法请参见PostgreSQL官方文档：
https://www.postgresql.org/docs/current/functions-json.xhtml#FUNCTIONS-SQLJSON-PATH 。
因为JSON类型没有提供相关的比较函数，所以无法在JSON类型的列上直接建索引，但可以在JSON类型的列上建函数索引。
JSONB类型的列上可以直接建索引。除了可以建BTree索引以外，JSONB还支持建GIN索引。GIN索引可以高效地从JSONB内部的key/value对中搜索数据。
通常情况下，在JSONB类型上都会考虑建GIN索引，而不是BTree索引，因为该索引的效率可能不高，原因是BTree索引不关心JSONB内部的数据结构，只是简单地按照比较整个JSONB大小的方式进行索引，其比较原则如下：
·Object>Array>Boolean>Number>String>Null。
·n个key/value对的Object>n-1个key/value对的Object。
·n个元素的Array>n-1个元素的Array。
Object内部的多个比较顺序如下：
key-1,value-1,key-2,value-2,…
键值之间的比较是按存储顺序进行的，示例如下：
{"aa":1,"a1":1}>{"b":1,"b1":1}
同样，数组是按元素的顺序进行比较的：
element-1,element-2,element-3,…
在JSONB上创建GIN索引有以下两种方式：
·使用默认的jsonb_ops操作符创建。
·使用jsonb_path_ops操作符创建。
使用默认的操作符创建GIN索引的语法如下：
CREATE INDEX idx_name ON table_name USING gin (index_col);
使用jsonb_path_ops操作符建GIN索引的语法如下：
CREATE INDEX idx_name ON table_name USING gin (index_col jsonb_path_ops);
关于GIN索引，jsonb_ops的GIN索引与jsonb_path_ops的GIN索引区别在于，jsonb_ops的GIN索引中JSONB数据中的每个key和value都是作为一个单独的索引项，而jsonb_path_ops则只为每个value创建一个索引项。例如：有一个项“{"foo":{"bar":"baz"}}”，对于jsonb_path_ops来说，是把“foo”“bar”和“baz”组合成一个Hash值作为索引项，而jsonb_ops则会分别为“foo”“bar”“baz”创建3个索引项。因为少了很多索引项，所以通常jsonb_path_ops的索引要比jsonb_ops的小很多，这样当然也就会带来性能的提升。
下面举例说明如何使用索引，先来看JSON类型上建函数索引的例子。
首先，创建测试表，并插放一些初始化的数据：
CREATE TABLE jtest01 (
id int,
jdoc json
);
CREATE OR REPLACE FUNCTION random_string(INTEGER)
RETURNS TEXT AS
$BODY$
SELECT array_to_string(
ARRAY (
SELECT substring(
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
FROM (ceil(random()*62))::int FOR 1
)
FROM generate_series(1, $1)
),
''
)
$BODY$
LANGUAGE sql VOLATILE;
insert into jtest01 select t.seq, ('{"a":{"a1":"a1a1", "a2":"a2a2"}, "name":"'||random_string(10)||'","b":"bbbbb"}')::json from generate_series(1,100000) as t(seq);
然后使用函数json_extract_path_text建一个函数索引：
CREATE INDEX ON jtest01 USING btree (json_extract_path_text(jdoc,'name'));
分析该表：
ANALYZE jtest01;
接下来看看查询没有走索引的执行计划：
osdba=# EXPLAIN ANALYZE VERBOSE SELECT * FROM jtest01 WHERE jdoc->>'name'='lnBtcJLR85';
QUERY PLAN
--------------------------------------------------------------------------------
Seq Scan on public.jtest01 (cost=0.00..2735.00 rows=500 width=36) (actual tim.
.e=17.764..154.870 rows=1 loops=1)
Output: id, jdoc
Filter: ((jtest01.jdoc ->> 'name'::text) = 'lnBtcJLR85'::text)
Rows Removed by Filter: 99999
Planning time: 0.141 ms
Execution time: 154.911 ms
(6 rows)
Time: 155.583 ms
然后再看走了此函数索引的执行计划：
osdba=# EXPLAIN ANALYZE VERBOSE SELECT * FROM jtest01 WHERE json_extract_path_text(jdoc, 'name') = 'lnBtcJLR85';
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using jtest01_json_extract_path_text_idx on public.jtest01 (cost=0.
..42..8.44 rows=1 width=36) (actual time=0.044..0.046 rows=1 loops=1)
Output: id, jdoc
Index Cond: (json_extract_path_text(jtest01.jdoc, VARIADIC '{name}'::text[]).
. = 'lnBtcJLR85'::text)
Planning time: 0.237 ms
Execution time: 0.085 ms
(5 rows)
Time: 1.153 ms
从上面的对比可以看出，走了索引花费的时间为1.153ms，而没有走索引花费的时间为155.583ms，可见走索引快了150倍左右。
下面再来看一个JSONB类型上建GIN索引的例子。
创建测试表如下：
CREATE TABLE jtest02 ( id int, jdoc jsonb ); CREATE TABLE jtest03 ( id int, jdoc jsonb );
把前面建好的测试表“jtest01”中的数据导入表“jtest02”和“jtest03”中：
insert into jtest02 select id, jdoc::jsonb from jtest01; insert into jtest03 select * from jtest02;
下面创建GIN索引：
CREATE INDEX idx_jtest02_jdoc ON jtest02 USING gin (jdoc); CREATE INDEX idx_jtest03_jdoc ON jtest03 USING gin (jdoc jsonb_path_ops);
然后对表进行分析：
ANALYZE jtest02; ANALYZE jtest03;
执行下面的查询：
SELECT * FROM jtest02 WHERE jdoc @> '{"name":"lnBtcJLR85"}'
SELECT * FROM jtest03 WHERE jdoc @> '{"name":"lnBtcJLR85"}'
现在来看一下这两个SQL语句的执行计划：
osdba=# EXPLAIN ANALYZE VERBOSE SELECT * FROM jtest02 WHERE jdoc @> '{"name":"lnBtcJLR85"}';
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on public.jtest02 (cost=1108.78..1424.97 rows=100 width=89) .
.(actual time=7.108..7.109 rows=1 loops=1)
Output: id, jdoc
Recheck Cond: (jtest02.jdoc @> '{"name": "lnBtcJLR85"}'::jsonb)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_jtest02_jdoc (cost=0.00..1108.75 rows=100 widt.
.h=0) (actual time=7.092..7.092 rows=1 loops=1)
Index Cond: (jtest02.jdoc @> '{"name": "lnBtcJLR85"}'::jsonb)
Planning time: 0.141 ms
Execution time: 7.162 ms
(8 rows)
Time: 7.883 ms
osdba=# EXPLAIN ANALYZE VERBOSE SELECT * FROM jtest03 WHERE jdoc @> '{"name":"lnBtcJLR85"}';
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on public.jtest03 (cost=1884.78..2200.97 rows=100 width=89) .
.(actual time=12.297..12.298 rows=1 loops=1)
Output: id, jdoc
Recheck Cond: (jtest03.jdoc @> '{"name": "lnBtcJLR85"}'::jsonb)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_jtest03_jdoc (cost=0.00..1884.75 rows=100 widt.
.h=0) (actual time=12.280..12.280 rows=1 loops=1)
Index Cond: (jtest03.jdoc @> '{"name": "lnBtcJLR85"}'::jsonb)
Planning time: 0.140 ms
Execution time: 12.352 ms
(8 rows)
从上面的运行结果中可以看到，这两个SQL都走到了索引上。
接下来看看这两个索引的大小：
osdba=# select pg_indexes_size('jtest02');
pg_indexes_size
-----------------
24731648
(1 row)
Time: 0.503 ms
osdba=# select pg_indexes_size('jtest03');
pg_indexes_size
-----------------
11173888
(1 row)
Time: 0.458 ms
从上面的运行结果中可以看出，jsonb_path_ops类型的索引要比jsonb_ops的小。