1. 前言
目前新版本的PostgreSQL 和 Oracle 都有很多 NO-SQL 特性,代表性的是对 json 和数组的支持。本文将对照列举PostgreSQL 和Oracle 的JSON 和数组的常用功能。
2 JSON功能
2.1 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}');
2.2 JSON 的查询
如果你要查询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]') |
2.3 JSON的修改
下面是两种数据库中,添加,修改和删除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"}} |
2.4 JSON的比较
下面的是两种数据库中比较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"}') |
2.5 创建索引
我们可以为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)));
注意,索引条目是字符串类型的。
2.6 其他JSON 功能
下面是一些常用其他的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} |
|
3 数组功能
PostgreSQL 中提供了两种的数组可作为字段类型,一种是数组类型,一种是类型是jsonb的json数组。而Oracle则只支持JSON数组。
现在我们介绍这两种数据库中数组的知识和相关功能。
3.1 数组的类型
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]');
3.2 数组的查询
下面是几种常见的涉及数组的查询
| 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开始 |
3.3 数组的修改
下面是两种数据库中,常见的修改数组的方法:
| 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 不支持,需在上层应用中实现 |
3.4 数组的比较
下面是两种数据库中,常见的数组的比较方法:
| 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")') |
3.5 创建索引
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")');
3.5 其他数组功能
下面是一些常用其他的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多。
4 总结
本文介绍了PostgreSQL 和 Oracle 支持的json 和数组的常用功能。目前主流的关系型数据库不断加大对NOSQL 的支持。但在使用这些功能时,应该考虑它们的成熟程度以及是否能满足业务需求。
5 参考文献
[1] PostgreSQL全球开发小组. PostgreSQL 11.2 手册. 彭煜玮, 瀚高软件译.
[2] Oracle. JSON Developer's Guide.
[3] Oracle. SQL Language Reference. Functions