目前新版本的PostgreSQL 和 Oracle 都有很多 NO-SQL 特性，代表性的是对 json 和数组的支持。本文将对照列举PostgreSQL 和Oracle 的JSON 和数组的常用功能。
PostgreSQL 有原生类型json和jsonb。它们接受相同的输入格式。它们实际的主要差别是效率。json 数据类型存储输入的文本内容，处理函数在处理时必须将它解析为JSON；而jsonb数据以分解的二进制格式存储，这使得它由于添加了转换机制而在输入上稍微慢些，但是在处理上明显更快，因为不需要重新解析。jsonb支持索引，这是一个明显的优势。 实际应用中，我们使用jsonb类型。
Oracle中，JSON字段用varchar或clob类型表示。在创建了这样的字段后，需要用一个检查约束确保数据是真正的json格式的。
下面是在 PostgreSQL 中创建一个包含jsonb类型的表的示例：
create table tb_test
(
id int,
json_column jsonb,
);
insert into tb_test (id, json_column) values (1, '{"name":"zhangsan","gender":"male","age":22}');
下面是在 Oracle 中创建一个包含json字段的表的示例：
create table tb_test
(
id int,
json_column clob,
CHECK (json_column IS JSON)
);
insert into tb_test (id, json_column) values (1, '{"name":"zhangsan","gender":"male","age":22}');
如果你要查询JSON 中的值，则需要给出JSON 键的名称或键的完整路径。注意，对于JSON对象{"a":{"b":{"c": "foo"}}}，在PostgreSQL 中JSON键路径的表示为 {a,b,c}；而在Oracle中，表示为 $.a.b.c
下面是在这两种数据库中，JSON的几种常用的查询。
PostgreSQL 11
Oracle 19c
判断JSON的最外层是否包含某个键
{"a":1, "b":2}'::jsonb ? 'a'
json_exists('{"a":1, "b":2}', '$.a')
给定一个键，它的值是普通文本
{"a":"1", "b":{"c": "foo"}}' ->> 'a'
结果：
1
json_value('{"a":"1", "b":{"c": "foo"}}' , '$.a')
结果：
1
给定一个键，它的值是一个json对象或json数组
’{"a":"1", "b":{"c": "foo"}}‘ -> 'b' （返回值的类型是jsonb/json）
结果：{"c": "foo"}
或
'{"a":"1", "b":{"c": "foo"}}' ->> 'b' （返回值的类型是text）
结果：{"c": "foo"}
json_query('{"a":"1", "b":{"c": "foo"}}' , '$.b')
结果：{"c": "foo"}
给定一个键的路径，它的值是普通文本
{"a":"1", "b":{"c": "foo"}}' #>> ‘{b, c}’
结果：foo
json_value('{"a":"1", "b":{"c": "foo"}}' , '$.b.c')
给定一个键的路径，它的值是 json 对象或json数组格式
{"a":"1", "b":{"c": "foo"}, "d":{"e":{"f":"1"}}}' #> ‘{d, e}’
（返回值的类型是jsonb/json）
结果：{"f":"1"}
或
{"a":"1", "b":{"c": "foo"}, "d":{"e":{"f":"1"}}}' #>> ‘{d, e}’
（返回值的类型是text）
结果：{"f":"1"}
json_query('{"a":"1", "b":{"c": "foo"}, "d":{"e":{"f":"1"}}}', '$.d.e')
结果：{"f":"1"}
取 json 数组中的第一个元素，
注意数组的下标从0开始
'{"a":[1,2,3,4]}'::jsonb #>> '{a,0}'
json_value('{"a":[1,2,3,4]}','$.a[0]')
下面是两种数据库中，添加，修改和删除JSON 键值对的方法：
PostgreSQL 11
Oracle 19c
合并或插入json 键值对
{"a":"1"}'::jsonb || '{"b":"1"}'::jsonb
结果：{"a":"1", "b" :"1"}
JSON_MERGEPATCH('{"a":"1"}', '{"b":"1"}')
结果：{"a":"1", "b" :"1"}
删除json 最外层的键值对
{"a":1, "b":2}'::jsonb - 'b'
结果：{"a":"1"}
JSON_MERGEPATCH('{"a":"1", "b" :"1"}', '{"b":null}')
结果：{"a":"1"}
修改json 最外层键值对
{"a":"1", "b":"2"}'::jsonb || '{"b":"1"}'::jsonb
结果：{"a":"1", "b" :"1"}
JSON_MERGEPATCH('{"a":"1", "b":"2"}', '{"b":"1"}')
结果：{"a":"1", "b" :"1"}
根据键的路径在JSON中插入或修改值
jsonb_set('{"a":1,"b":{"c":"1"}}', '{b,c}','"2"', false)
结果：
{"a": 1, "b": {"c": "2"}}
JSON_MERGEPATCH('{"a":1,"b":{"c":"1"}}', '{"b":{"c":"2"}}')
结果：
{"a": 1, "b": {"c": "2"}}
下面的是两种数据库中比较json的方法，返回结果是布尔类型。
PostgreSQL 11
Oracle 19c
比较json是否相等
'{"a":"1"}' :: jsonb = '{"a":"1"}'::jsonb
json_equal('{"a":"1"}', '{"a":"1"}')
左边的json 是否
包含右边的json
{"a":1, "b":2}'::jsonb @> '{"a":1}'::jsonb
json_equal(JSON_MERGEPATCH('{"a":"1", "b" :"1"}', '{"a":"1"}'), '{"a":"1", "b" :"1"}')
我们可以为JSON 字段（存放JSON对象）的某些键创建索引
例如，要为表tb_test 中 json_column 的属性 age创建索引。
在PostgreSQL中，这样的SQL语句是：
create index idx_tb_test_json_column on tb_test ((json_column ->> 'age'));
在Oracle中，这样的SQL语句是：
create index idx_tb_test_json_column on tb_test (json_value(json_column, '$.age' RETURNING varchar(64)));
注意，索引条目是字符串类型的。
下面是一些常用其他的JSON功能：
PostgreSQL 11
Oracle 19c
备注
创建一个json对象
jsonb_build_object('a',‘1’,'b',‘2’)
结果： {"a": "1", "b": "2"}
json_object(key 'a' value '1', key 'b' value '2')
结果： {"a": "1", "b": "2"}
把一个json中的键变成列
select * from jsonb_populate_record(null::myrowtype, '{"a":1,"b":2}')
结果：
a | b
---+---
1 | 2
SELECT * FROM json_table('{"a":1, b:"2"}', '$' columns (a PATH '$.a', b PATH '$.b'))
结果：
a | b
---+---
1 | 2
可以看出，Oracle中把json键转成列，需要指定列名和json的键。
可以看出，Oracle中把json键转成列，需要指定列名和json的键。
把一个含有json数组中的json对象的键变成列
select * from jsonb_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]')
结果：
a | b
---+---
1 | 2
3 | 4
SELECT * FROM json_table('[{"a":1,"b":2},{"a":3,"b":4}]', '$[*]' columns (a PATH '$.a', b PATH '$.b'))
结果：
a | b
---+---
1 | 2
3 | 4
可以看出，Oracle中把json键转成列，需要指定列名和json的键。
将N列多行数据聚集为一个json对象
select jsonb_object_agg(name, id)
from tb_man;
结果：
{"Tom":1,"Bob":2}
select json_objectagg(KEY name VALUE id) from tb_man;
结果：
{"Tom":1,"Bob":2}
PostgreSQL 中提供了两种的数组可作为字段类型，一种是数组类型，一种是类型是jsonb的json数组。而Oracle则只支持JSON数组。
现在我们介绍这两种数据库中数组的知识和相关功能。
PostgreSQL 支持的数组有两种，一种是数组类型，一种是json数组。
下面的案例是创建表 tb_test，它的字段 code 的类型是整型数组类型：
create table tb_test
(
id int,
codes integer [],
);
insert into tb_test (id, codes) values (1, '{1, 2, 3, 4}');
insert into tb_test (id, codes) values (2, array[1, 2, 3, 4]);
下面的案例是创建表tb_test，它的字段 code 的类型是jsonb，格式是json数组：
create table tb_test
(
id int,
codes jsonb
);
insert into tb_test (id, codes) values (1,'[1, 2, 3, 4]');
而Oracle中，json数组的类型通常是 varchar2/varchar 。创建包含数组字段的表的示例如下：
create table tb_test
(
id int,
codes varchar(512),
CHECK (attribute IS JSON)
);
insert into tb_test (id, json_column) values (1, '[1, 2, 3, 4]');
下面是几种常见的涉及数组的查询
PostgreSQL 11
Oracle 19c
数组的类型
array 类型的数组
json 数组，类型为jsonb
json 数组，类型为varchar
判断数组是否包含某个元素
array[1,2,3,4] @> array[1]
cast('[1, 2, 3, 4]' as jsonb) @> cast('[1]' as jsonb)
json_exists('[1, 2, 3]','$?(@ == "1")')
取数组中的第一个元素
select (array[1,2,3,4])[1];
注意，数组下标从1开始，[1] 表示第1个元素
select '[1,2,3,4]'::jsonb #>> '{0}'
注意，数组下标从0开始
SELECT json_value('[1,2,3,4]','$[0]') from dual
注意，数组下标从0开始
下面是两种数据库中，常见的修改数组的方法：
PostgreSQL 11
Oracle 19c
数组的类型
array 类型的数组
json 数组，类型为jsonb
json 数组，类型为varchar
向数组末端追加一个元素
array_append(array[1,2], 3)
或
array[1,2] || array[3]
cast( '[1, 2]' as jsonb) || cast( '[3]' as jsonb)
substr('[1, 2]', 1, length('[1, 2]') - 1) || '3]'
合并两个数组
array[1,2] || array[3, 4]
或
array_cat(array[1,2], array[3,4])
cast( '[1, 2]' as jsonb) || cast( '[3, 4]' as jsonb)
substr('[1, 2]', 1, length('[1, 2]') - 1) || '3, 4]'
从数组中删除值为2的元素
array_remove(ARRAY[1,2,3,2], 2)
结果：
{1,3}
不支持，需在上层应用中实现
Oracle 不支持，需在上层应用中实现
下面是两种数据库中，常见的数组的比较方法：
PostgreSQL 11
Oracle 19c
数组的类型
array 类型的数组
json 数组，类型为jsonb
json 数组，类型为varchar
比较数组是否相等
array[1,2,3,4] = array[1,2,3]
cast('[1, 2, 3, 4]' as jsonb) = cast('[1,2,3]' as jsonb)
json_equal('[1, 2, 3, 4]', '[1,2,3]')
判断左边的数组是否包含右边的数组
array[1,2,3,4] @> array[1,2]
cast('[1, 2, 3, 4]' as jsonb) @> cast('[1,2]' as jsonb)
不支持直接比较数组，需要把右边的数组拆分：
json_exists('[1, 2, 3]','$?(@ == "1")') and json_exists('[1, 2, 3]','$?(@ == "2")')
PostgreSQL 支持创建包含数组中全部元素的索引。无论数原生数组类型，还是jsonb类型。创建数组示例如下：
create index idx_tb_test_codes on tb_test using gin (codes);
这个索引支持在字段codes以任意元素值或数组作为条件进行查询。
例如,对原生数组类型的字段：
Select * from tb_test where codes @> array[1,2];
对jsonb类型的字段：
Select * from tb_test where codes @> cast('[1,2]' as jsonb);
而oracle 不支持创建包含数组中全部元素的索引。只支持为数组上的某个元素创建基于表达式的索引。
例如：
create index idx_tb_test_codes on tb_test (json_exists(codes, '$?(@ == "1")'));
这样的索引，只对 json_exists(codes, '$?(@ == "1")') 为条件的查询有效。例如：
Select * from tb_test where json_exists(codes, '$?(@ == "1")');
下面是一些常用其他的JSON功能：
PostgreSQL 11
Oracle 19c
数组的类型
array 类型的数组
json 数组，类型为jsonb
json 数组，类型为varchar
计算数组的元素个数
select array_length(array[1,2,3], 1)
select jsonb_array_length('[1, 2, 3, 4]')
SELECT count(*) FROM json_table('[1, 2, 3]', '$[*]' COLUMNS (value PATH '$'))
将不同的行聚合成一个数组
select array_agg(code) from tb_device where device_type = 'super';
select jsonb_agg(code) from tb_device where device_type = 'super';
select json_arrayagg(code) from tb_device where device_type = 'super';
将数组元素转成多个行
select unnest(array[1,2,3]);
select cast(jsonb_array_elements('[1,2,3]' as text);
SELECT value
FROM json_table( '[1, 2, 3]' , '$[*]' COLUMNS (value PATH '$') )
从上面的例子可以看出，PostgreSQL 11 对 数组的支持要比Oracle多。
本文介绍了PostgreSQL 和 Oracle 支持的json 和数组的常用功能。目前主流的关系型数据库不断加大对NOSQL 的支持。但在使用这些功能时，应该考虑它们的成熟程度以及是否能满足业务需求。
[1] PostgreSQL全球开发小组. PostgreSQL 11.2 手册. 彭煜玮， 瀚高软件译.
[2] Oracle. JSON Developer's Guide.
[3] Oracle. SQL Language Reference. Functions
